CODEWITHSUNDEEP
mysqlsql
LaggKing
LaggKing

Reputation: 202

sql query to list all the items in a group in one record

I need to write a query that will return to me all the items in a group in one record, separated by commas,from two tables, example result below,

Items table:

--------------------
Name    |  Group_ID
--------------------
item1   |  1
item2   |  1
item3   |  3

Group table:

--------------------
ID    |  Name
--------------------
 1    |  Group1
 3    |  Group3

Result i'm looking for:

------------------------------
GId  |  Items  
------------------------------
 1   |  item1, item2
 3   |  item3

Upvotes: 6

Views: 5838

Answers (3)

aleroot
aleroot

Reputation: 72646

USE GROUP_CONCAT

SELECT group_concat(Name) FROM table

Upvotes: 10

peterm
peterm

Reputation: 92805

According to your desired output and assuming that you always have at least one item per group or if your nat interested in groups that don't have items yet you don't even need to join your tables. Just apply GROUP_CONCAT() to items table

SELECT group_id gid, GROUP_CONCAT(Name) items
  FROM items
 GROUP BY group_id

Output:

| GID |       ITEMS |
|-----|-------------|
|   1 | item1,item2 |
|   3 |       item3 |

Here is SQLFiddle demo

Now if you need to select all groups no matter they have items or not then you have to use an outer join as in @0r10n's answer

Here is SQLFiddle demo for that scenario.

Upvotes: 0

Augusto
Augusto

Reputation: 819

You need to use GROUP_CONCAT and a GROUP BY

It would be something like this:

SELECT gr.id, GROUP_CONCAT(item.name SEPARATOR ',')
FROM  `group` gr LEFT JOIN item
ON(gr.id=item.group_id)
GROUP BY gr.id

This query will display groups that don't have items associated. If you don't need those groups, then the best option is @peterms

Upvotes: 0

Related Questions