3

I have a table (Table D) that has a field that has another table (Table M) associated with it which contains a set of rows that reference a single row in table D.

Can (and if so, how) can I create a SQL Query that will not only select the fields from D, but join with table M, returning all the row values (only from one column) in a single result column, but all concatenated together (preferably with some sort of escaping so it can't be contaminated by rogue data in M)?

EDIT:

+-----------+----------+-------------+
| DataRowID | DataName | RandomField |
+-----------+----------+-------------+
| 1         | Foo      | Alice       |
+-----------+----------+-------------+
| 2         | Bar      | Bob         |
+-----------+----------+-------------+
| 3         | Baz      | Charlie     |
+-----------+----------+-------------+


+-----------+-----------+-----------+
| MetaRowID | DataRowID | MetaValue |
+-----------+-----------+-----------+
| 1         | 1         | Mercury   |
+-----------+-----------+-----------+
| 2         | 1         | Venus     |
+-----------+-----------+-----------+
| 3         | 1         | Earth     |
+-----------+-----------+-----------+
| 4         | 2         | Mars      |
+-----------+-----------+-----------+
| 5         | 2         | Jupiter   |
+-----------+-----------+-----------+
| 6         | 3         | Saturn    |
+-----------+-----------+-----------+
| 7         | 3         | Uranus    |
+-----------+-----------+-----------+
| 8         | 3         | Neptune   |
+-----------+-----------+-----------+
| 9         | 3         | Pluto     |
+-----------+-----------+-----------+

Which when Where DataName = 'Bar' is Queried,

+-----------+----------+-------------+--------------+
| DataRowID | DataName | RandomField | MetaData     |
+-----------+----------+-------------+--------------+
| 2         | Bar      | Bob         | Mars,Jupiter |
+-----------+----------+-------------+--------------+
topherg
  • 4,203
  • 4
  • 37
  • 72

1 Answers1

2

Use MySql's GROUP_CONCAT function:

SELECT d.ID, GROUP_CONCAT(m.Field) AS ConcatFields
FROM d JOIN m ON d.ID = m.dID
GROUP BY d.ID
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • For the GROUP_CONCAT, how does it glue the values together? also, let's say it just uses `,`s and there is a `,` in the field value, does it get escaped? – topherg Dec 19 '12 at 16:05
  • 1
    @cgoddard The default separator is a `,`, but you can pass a different separator to the function as well. It won't change any of the existing data in the field, so you'll need to `REPLACE` any existing `,`s in the field. – Michael Fredrickson Dec 19 '12 at 16:15
  • Thanks. I have looked at http://stackoverflow.com/questions/452357/mysql-group-concat-escaping as an implementation of such a thing, do you agree? – topherg Dec 19 '12 at 16:18