How to join MySQL tables?

Question

I basically have two tables:

A(id int, name varchar(10), info varchar(10))
B(id int, item varchar(10))

A
1 A Hello
2 B World
3 C Foo

B
1 apple
1 orange
1 hammer
2 glass
2 cup

Note that C doesn't have any items. I want to join the two tables to get this:

1 A Hello apple|orange|hammer
2 B World glass|cup
3 C Foo null

I am familiar with the GROUP_CONCAT(item SEPARATOR '|') concept but I'm not quite sure how to write the query, especially to get the row with 3 C Foo null

Answer 1

mysql> SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR '|')
       FROM a
       LEFT OUTER JOIN b ON (a.id=b.id)
       GROUP BY 1,2,3;                                                             
+------+------+-------+------------------------------------+
| id   | name | info  | group_concat(b.item separator '|') |
+------+------+-------+------------------------------------+
|    1 | A    | Hello | apple|orange|hammer                | 
|    2 | B    | World | glass|cup                          | 
|    3 | C    | Foo   | NULL                               | 
+------+------+-------+------------------------------------+

Outer joins explained: http://en.wikipedia.org/wiki/Join%5F%28SQL%29#Outer%5Fjoins

Answer 2

SELECT a.id, a.name, a.info, GROUP_CONCAT( b.name SEPARATOR '|' ) FROM A AS a LEFT OUTER JOIN B AS b ON b.id = a.id GROUP BY a.id

returns

id name info group_concat(b.name SEPARATOR '|')
1 A Hello apple|orange|hammer
2 B World glass|cup
3 C Foo NULL