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...
Pavel
On Tue, Jul 28, 2009 at 7:21 PM, P Kishor<[email protected]> 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
