Following up on my recent question about arbitrarily tagging rows in a
table, my website has certain pages that are tagged as 'Photos' and
other tags (http://www.punkish.org/Photos). The table schema is as
follows
TABLE pages (p_id INTEGER PRIMARY KEY, p_name TEXT);
TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
TABLE pages_tags (p_id INTEGER, t_id INTEGER);
Given a parameter ? = 'current_tag'
Find all the non-'Photos' tags and their Counts for the pages that are
tagged as 'Photos' (tag_id = 1);
If the tag is the same as the 'current_tag', add a 'classtype' of
'tag_a', else 'tag_b' (the effect of the 'classtype' can be seen by
clicking on any of the tags and seeing the color of the relevant tags
change);
SELECT
-- set 'classtype' to 'tag_a' or 'tag_b'
CASE
WHEN t.tag_id IN (
SELECT DISTINCT tag_id
FROM pages_tags
WHERE page_id IN (
SELECT p.page_id
FROM pages p JOIN pages_tags pt ON p.page_id = pt.page_id
WHERE pt.tag_id = ?
)
)
THEN 'tag_a'
ELSE 'tag_b'
END AS classtype,
t.tag_id,
t.tag_name,
Count(tag_name) AS tag_num
FROM tags t JOIN pages_tags pt ON t.tag_id = pt.tag_id
WHERE
-- all tags that are not 'Photos'
t.tag_id != 1 AND
-- all pages that are tagged as 'Photos'
pt.page_id IN (
SELECT page_id FROM pages_tags WHERE tag_id = 1
)
GROUP BY classtype, t.tag_id, tag_name
ORDER BY tag_name
Question: The above works just fine, but seems awfully convoluted,
which could be a result of my thinking too much about it.
Particularly, it seems the two nested SELECTs in the CASE clause would
be executed for each row in the result set. Could I do this more
elegantly?
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
