Selecting the items associated with a tag along with the tags associated with these items

Question

2 tables:
items(item_id, ...)
tags(tag_id, item_id)

How do I select the items (from the "items" table) that have a particular tag associated with them (in the "tags" table) along with all the tags associated with these items?

Answer 1

Use:

SELECT i.*, 
       t.tag_id
  FROM ITEMS i
  JOIN TAGS t ON t.item_id = i.item_id
 WHERE i.item_id IN (SELECT x.item_id
                       FROM TAGS x 
                      WHERE x.tag_id = ?)