mysql - select multiple rows in one result row

Question

In one of my tables i store my advertisement data, thats one row per advertisement. I also store some dates in an other table, but that's one row per date because i don't know howmany dates a specific advertisement gets. I want to select al the dates (where ID adventisement = 1) in the same query as the data selection, seperated bij a komma. Only problem is that i get as many rows as there are dates, i only want one row with al the data…..

Table 1 (Advertisements)
ID_adv         data 1             data2
1              name1              picture1
2              name2              picture2  
3              name3              picture3
4              name4              picture4

Table 2 (Dates)
ID  ID_adv      date
1     2     1-1-2012
2     2     2-1-2012
3     3     1-1-2012
4     3     2-1-2012
5     3     3-1-2012
6     3     4-1-2012

Outcome query (Select ID_adv, data1, data2, dates WHERE ID_adv = 3)

3,name3,picture3,"1-1-2012,2-1-2012,3-1-2012,4-1-2012"

The dates column can be one string with al the dates seperated by a comma….

Any ideas?

Answer 1

You can use GROUP_CONCAT() and GROUP BY to get the results you desire:

SELECT t1.*, GROUP_CONCAT(t2.date) AS dates
FROM Table1 t1
LEFT JOIN Table2 t2
  ON t2.ID_adv = t1.ID_adv
GROUP BY t1.ID_adv

This returns all the dates for each advertisement, concatenated by commas. Where there are no dates in Table2 for a particular advertisment, you'll get NULL for the dates column.

To target a particular advertisement, simply add the WHERE clause:

SELECT t1.*, GROUP_CONCAT(t2.date) AS dates
FROM Table1 t1
LEFT JOIN Table2 t2
  ON t2.ID_adv = t1.ID_adv
WHERE t1.ID_adv = 3
GROUP BY t1.ID_adv