fuzztrek wrote:
> Forgive my ignorance, but is there anyway to make
> this work so it will select icons related to one
> keyword but not the other? (e.g. X but not Y, or Y
> but not X)
No need to ask forgiveness! That's exactly why the forums are here: to help. :)
Instead of INNER JOIN (which produces the intersect), use a LEFT JOIN ... WHERE ... IS NULL, which will get the results in the left table not containing a match in the right table... The trick is to use the filter on keyword in the join condition, not the WHERE condition (see below)
so to get all matching 'X' and not having 'Y', use:
SELECT DISTINCT i.filename
FROM icons i, keywords k1, keywords k2
INNER JOIN rel_icon_keyword rik1
ON i.icon_id = rik1.icon_id
AND rik1.keyword_id = k1.keyword_id
LEFT JOIN rel_icon_keyword rik2
ON i.icon_id = rik2.icon_id
AND k2.keyword_id = rik2.keyword_id
AND k2.keyword_name = 'Y'
WHERE k1.keyword_name = 'X'
AND rik2.keyword_id IS NULL;
simply do the inverse to find all matching 'Y' and not having 'X':
SELECT DISTINCT i.filename
FROM icons i, keywords k1, keywords k2
INNER JOIN rel_icon_keyword rik1
ON i.icon_id = rik1.icon_id
AND rik1.keyword_id = k1.keyword_id
LEFT JOIN rel_icon_keyword rik2
ON i.icon_id = rik2.icon_id
AND k2.keyword_id = rik2.keyword_id
AND k2.keyword_name = 'X'
WHERE k1.keyword_name = 'Y'
AND rik2.keyword_id IS NULL;
Hope this helps,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com