CODEWITHSUNDEEP
mysqldatabasejoin
TiPE
TiPE

Reputation: 412

MySQL: Get all Items and associated tags with just one request

I have two MySQL tables. One contains Items (format: id|name|...) the other contains tags which belong to the items (format: id|item_id|name|...). There is one row for each 'use' of a tag. One item can have many tags.

Is it possible (e.g. By smart use of JOIN) to get all Items including their associated tags with just one request?

What I want to have:

Upvotes: 1

Views: 704

Answers (1)

AdamMc331
AdamMc331

Reputation: 16730

This can easily be done with a join, all you have to do is use the item_id as the related column:

SELECT i.id, i.name, t.name
FROM items i
JOIN tags t ON t.item_id = i.id;

If you want to see all tags for an item grouped together, you can consider using the GROUP_CONCAT() function:

SELECT i.id, i.name, GROUP_CONCAT(t.name) AS tags
FROM items i
JOIN tags t ON t.item_id = i.id
GROUP BY i.id;

This will give you a comma separated list of all the tags for each item. Note that the above query will only return items that do have tags. If you want to see all items, regardless of whether or not they have a tag associated with them, you should use a LEFT JOIN.

Upvotes: 4

Related Questions