> 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?
AFAIK, when you're writing your FROM clause in the form "table1 JOIN table2 ON condition" you can write pretty much any condition on table2 right here in the FROM clause (I don't remember exactly but maybe you can put condition on table1 here too). But when you're writing query in the form "FROM table1, table2 WHERE conditions" then yes, of course, all conditions should go into WHERE clause. And this is exactly why I don't like "table1 JOIN table2" form (one can easily confuse himself by putting related conditions to different places) and write my queries always in the form "FROM table1, table2". In this particular case I've decided to continue to write in your syntax preference and put related conditions (pt_p.page_id = pt_np.page_id AND pt_np.tag_id != 1) in one place because I think when they stand nearby intention of the query is more understandable. Pavel On Tue, Jul 28, 2009 at 9:59 PM, P Kishor<punk.k...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
