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

2 table开发者_如何学编程s:

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?

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 = ?)