Pavel,
On Tue, Jul 28, 2009 at 7:45 PM, Pavel Ivanov<paiva...@gmail.com> wrote: > First of all you've mentioned schema which is not in agreement > slightly with sql you've given. But of course we can guess... > Second your sql seems to do slightly different thing from what you're > saying. In particular I'm concerned about this sentence: > >> 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); > > What sql does is: if tag is among those assigned to pages having also > 'current_tag' then add a classtype 'tag_a' else 'tag_b'. You are absolutely correct about both of the above. The first error (wrong schema) was because of me copying the concept schema from my earlier email and juxtaposing it with my real SQL query... but yes, p_id is the same as page_id and t_id is the same as tag_id. The second error is because of my mind being fixated on an earlier iteration of my solution that applied 'tag_a' only when the 'current_tag' matched the tag in the row. Of course, that is not what I wanted... I wanted to display *all* the tags, but highlight only all those tags that matched current rows being displayed. In any case, your sharp eye caught both errors. I will study and try out your suggested statement. Many thanks for that. > > But keeping all that in mind if I were you I'd write select in the > following way: > > SELECT > CASE WHEN t_cur.tag_id is null THEN 'tag_b' ELSE 'tag_a' > END AS classtype, > t.tag_id, > t.tag_name, > count(*) tag_num > FROM pages_tags pt_p > JOIN pages_tags pt_np on pt_p.page_id = pt_np.page_id > AND pt_np.tag_id != 1 > JOIN tags t on pt_np.tag_id = t.tag_id > LEFT OUTER JOIN ( > SELECT DISTINCT pt_pcur.tag_id > FROM pages_tags pt_cur > JOIN pages_tags pt_pcur on pt_pcur.page_id = > pt_cur.page_id > WHERE pt_cur.tag_id = ?) t_cur > on t_cur.tag_id = t.tag_id > WHERE pt_p.tag_id = 1 > GROUP BY t.tag_id, t.tag_name > ORDER BY t.tag_name > > It eliminates executing of select for each row and also shows clearly > how it's intended to be executed... > > Pavel > > On Tue, Jul 28, 2009 at 7:21 PM, P Kishor<punk.k...@gmail.com> wrote: >> 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 >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
