MySQL Query for Item List View with tags

Question

Hi what is the right query for this result:

+---------------------------------------+
item_id | item_title | tag_list         |
----------------------------------------+
1       |  Title 1   | tag1 , tag2, tag3|
2       |  Title 2   | tag7 , tag2      |
3       |  Title 3   | tag9 , tag5, tag4|
4       |  Title 4   | tag7 , tag6, tag3|
-----------------------------------------

I have the following tables:

• items -> item_id (PK), item_title
• tags -> tag_id (PK), tag_name (unique)
• items_tags_xref -> items_tags_xref_id (PK), item_id , tag_id

SQL Fiddle -> http://sqlfiddle.com/#!2/33dea8/1

I tried the following query with no success:

SELECT 
items.item_id, 
items.title, 
(
    SELECT GROUP_CONCAT(DISTINCT tags.tag_name)
        FROM tags 
            INNER JOIN items_tags_xref 
                ON tags.tag_id = items_tags_xref.tag_id
            INNER JOIN items
                ON items_tags_xref.item_id = items.item_id
        WHERE items_tags_xref.item_id = items.item_id

) AS tag_list
FROM items

The result of the above query is showing all the tag_names inside tag_list

But if I do this query:

SELECT 
items.item_id, 
items.title, 
(
    SELECT GROUP_CONCAT(DISTINCT tags.tag_name)
        FROM tags 
            INNER JOIN items_tags_xref 
                ON tags.tag_id = items_tags_xref.tag_id
            INNER JOIN items
                ON items_tags_xref.item_id = items.item_id
        WHERE items_tags_xref.item_id = 4

) AS tag_list
FROM items

"4 is a specific item_id" I get the correct result only for that item. If only 4 is dynamic when I use items.item_id inside tag_list..

I'm trying to solve this problem for days and been searching for an answer in google but can't find anything. Maybe i'm using the wrong keywords :( but if anybody can give me at least a tip on how to do this right. it would be really helpful. thanks!

• P.S. i'm new in mysql

Answer 1

I'm a little confused, what part of the problem does the following not solve?

SELECT i.*
     , GROUP_CONCAT(tag_name) tags
  FROM items i
  JOIN items_tags_xref it
    ON it.item_id = i.item_id
  JOIN tags t
    ON t.tag_id = it.tag_id
 GROUP
    BY i.item_id;

http://sqlfiddle.com/#!2/33dea8/6

Also, your surrogate key on the items_tags_xref table seems entirely redundant, as the remaining columns would serve as a perfectly viable composite natural PRIMARY KEY