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'. > > 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...
Yes, indeed. Your query works well. One question... what is happening with 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 I can't understand what that != 1 constraint is doing in the FROM clause. Shouldn't a constraint be in the WHERE clause while the FROM clause should only describe the source tables? > > 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 >> > -- 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 ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
