CODEWITHSUNDEEP
sql
jhickner
jhickner

Reputation: 1053

Selecting by tags while also returning all tags for the selected items

I have the basic tagging table setup:

item (id, body)
tag_link (id, item_id, tag_id)
tag (id, name)

I can work out the JOIN to select for all items matching a list of tags:

SELECT i.* FROM item AS i
INNER JOIN tag_link AS tl
ON tl.item_id = i.id
AND tl.tag_id IN
  (SELECT tag.id FROM tag WHERE tag.name IN ('red', 'blue'))
GROUP BY i.id
HAVING COUNT(i.id) = 2

The problem is that I also want all the tags associated with the selected items.

I ran across the GROUP_CONCAT function (I'm using SQLite), and came up with this:

SELECT i.*, GROUP_CONCAT(t.name) 
FROM item AS i
INNER JOIN tag_link AS tl
ON tl.item_id = i.id
AND tl.tag_id IN
  (SELECT tag.id FROM tag WHERE tag.name IN ('red', 'blue'))
INNER JOIN tag AS t ON t.id = tl.tag_id
GROUP BY i.id
HAVING COUNT(i.id) = 2

Which adds a field that looks like "red, blue" to each item returned. So far so good.

But if the items also have other tags associated with them that weren't part of the query, they won't get returned. So my question is, how would I include ALL the tags for the returned items?

Thanks!

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use a having clause instead. If you want all the tags for items that have either red or blue, then use:

SELECT i.*, GROUP_CONCAT(t.name) 
FROM item i INNER JOIN
     tag_link tl
     ON tl.item_id = i.id INNER JOIN
     tag t
     ON t.id = tl.tag_id
GROUP BY i.id
HAVING sum(t.name IN ('red', 'blue')) > 0;

Upvotes: 1

Related Questions