Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
~aanzx
.
Toggle Highlighting
SQL
WITH RECURSIVE subcategories AS ( SELECT cl_from AS subcat_id FROM categorylinks WHERE cl_to = 'ವರ್ಷಗಳ_ಪ್ರಕಾರ_ಕನ್ನಡ_ಚಲನಚಿತ್ರಗಳು' AND cl_type = 'subcat' UNION ALL SELECT cl_from FROM categorylinks JOIN subcategories ON cl_to = page_title WHERE cl_type = 'subcat'),pages_in_subcategories AS ( SELECT page_id, page_title FROM page JOIN categorylinks ON cl_from = page_id WHERE cl_from IN (SELECT subcat_id FROM subcategories) AND page_namespace = 0)SELECT CONCAT('https://kn.wikipedia.org/wiki/', REPLACE(page_title, ' ', '_')) AS page_linkFROM pages_in_subcategories;You said:Unknown column 'page_title' in 'on clause'You’re giving feedback on a new version of ChatGPT.Which response do you prefer? Responses may take a moment to load.ChatGPTResponse 1The issue arises because the page_title column is not directly available in the categorylinks table. To resolve this, we need to join the categorylinks table with the page table to fetch page_title. Here's the corrected query:sqlCopy codeUSE knwiki_p;WITH RECURSIVE subcategories AS ( SELECT cl_from AS subcat_id, p1.page_title AS subcat_title FROM categorylinks JOIN page AS p1 ON cl_from = p1.page_id WHERE cl_to = 'ವರ್ಷಗಳ_ಪ್ರಕಾರ_ಕನ್ನಡ_ಚಲನಚಿತ್ರಗಳು' AND cl_type = 'subcat' UNION ALL SELECT cl_from, p2.page_title FROM categorylinks JOIN page AS p2 ON cl_from = p2.page_id JOIN subcategories ON cl_to = subcategories.subcat_title WHERE cl_type = 'subcat'),pages_in_subcategories AS ( SELECT p3.page_id, p3.page_title FROM page AS p3 JOIN categorylinks ON cl_from = p3.page_id WHERE cl_from IN (SELECT subcat_id FROM subcategories) AND p3.page_namespace = 0)SELECT CONCAT('https://kn.wikipedia.org/wiki/', REPLACE(page_title, ' ', '_')) AS page_linkFROM pages_in_subcategories;Key Fixes:Joins with page table:In the subcategories CTE, the categorylinks table is joined with the page table to access page_title.Alias for page table:Aliases (p1, p2, p3) are used to differentiate between multiple uses of the page table in the query.Use of subcat_title:The recursive part now references subcat_title from the previous level to find child subcategories.This corrected query should now work without errors.I prefer this responseChatGPTResponse 2Thought for 9 secondsApologies for the confusion and the error in the previous query. The issue arises because the page_title column isn't directly available in the categorylinks table when performing the join. To resolve this, we need to join the categorylinks table with the page table to access the page_title.Here's the corrected query for listing pages in subcategories of the category ವರ್ಷಗಳ ಪ್ರಕಾರ ಕನ್ನಡ ಚಲನಚಿತ್ರಗಳು:sqlCopy codeUSE knwiki_p;WITH RECURSIVE subcategories AS ( -- Start with subcategories of the root category SELECT cl_from AS subcat_id FROM categorylinks JOIN page ON categorylinks.cl_from = page.page_id WHERE cl_to = 'ವರ್ಷಗಳ_ಪ್ರಕಾರ_ಕನ್ನಡ_ಚಲನಚಿತ್ರಗಳು' AND cl_type = 'subcat' AND page.page_namespace = 14 -- Namespace 14 is for categories UNION ALL -- Recursively find subcategories of the subcategories SELECT cl.categorylinks.cl_from FROM categorylinks AS cl JOIN page AS p ON cl.cl_from = p.page_id JOIN subcategories AS sc ON cl.cl_to = p.page_title WHERE cl.cl_type = 'subcat' AND p.page_namespace = 14),pages_in_subcategories AS ( SELECT pg.page_id, pg.page_title FROM page AS pg JOIN categorylinks AS cl ON cl.cl_from = pg.page_id WHERE cl.cl_to IN ( SELECT p.page_title FROM page AS p WHERE p.page_id IN (SELECT subcat_id FROM subcategories) ) AND pg.page_namespace = 0 -- Namespace 0 is for main/article pages)SELECT CONCAT('https://kn.wikipedia.org/wiki/', REPLACE(page_title, ' ', '_')) AS page_linkFROM pages_in_subcategories;Explanation of the Corrections:Joining with the page Table:To access page_title, we need to join the categorylinks table with the page table using cl_from = page.page_id.This allows us to filter categories correctly and retrieve their titles.Namespace Filtering:Namespace 14: Represents categories. We ensure that we're only processing category pages when identifying subcategories.Namespace 0: Represents main/article pages. We target these when listing pages within subcategories.Recursive CTE (subcategories):Base Case: Selects subcategories directly under ವರ್ಷಗಳ_ಪ್ರಕಾರ_ಕನ್ನಡ_ಚಲನಚಿತ್ರಗಳು.Recursive Step: Finds subcategories of the already identified subcategories by matching cl_to with the page_title of categories.Selecting Pages in Subcategories:After identifying all subcategories, we select pages that belong to these subcategories.We construct the full URL for each page by concatenating the base URL with the page_title, replacing spaces with underscores as per MediaWiki URL formatting.Running the Query:Access Quarry:Navigate to Quarry and ensure you're connected to the knwiki_p (Kannada Wikipedia) database.Execute the Query:Paste the corrected SQL query into the query editor.Run the query to obtain a list of pages within the subcategories of ವರ್ಷಗಳ ಪ್ರಕಾರ ಕನ್ನಡ ಚಲನಚಿತ್ರಗಳು.Review Results:The output will display clickable links to each page in the specified subcategories.Additional Notes:Performance Considerations:Recursive queries can be resource-intensive, especially for categories with many nested subcategories. If you encounter performance issues, consider limiting the recursion depth or optimizing the query further.Category Names:Ensure that the category name 'ವರ್ಷಗಳ_ಪ್ರಕಾರ_ಕನ್ನಡ_ಚಲನಚಿತ್ರಗಳು' is correctly spelled and uses underscores (_) instead of spaces, as category names in URLs replace spaces with underscores.If you encounter any further issues or need additional modifications, feel free to ask!I prefer this responseChatGPT can make mistakes. Check im
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...