MYSQL request from two tables

Question

May be you can help me:

I have a tables:

1: Users
id   username   email
1    myname     myname@myname.com
2    myname2    myname2@myname.com

2: Accounts
user_id   account
1         37992054
2         3804933 
1         23286966

I need to make a request and get this data:

id   username   email                account
1    myname     myname@myname.com    37992054, 23286966
2    myname2    myname2@myname.com   3804933

Learn about [SQL joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html). You will need to use [`GROUP_CONCAT()`](http://dev.mysql.com/doc/en/group-by-functions.html#function_group-concat) with a `GROUP BY` clause. — eggyal, May 17 '12 at 12:11

score 4 · Accepted Answer · answered May 17 '12 at 12:13

4

You need to join your two tables and use a grouping construct to collapse records with the same user id:

Select u.id, u.username, u.email, group_concat(a.account)
from users as u join accounts as a 
  on a.user_id = u.id
group by u.id;

answered May 17 '12 at 12:13

D Mac

3,727
1
25
32

Great solution D Mac. Thumb up! Do you know how to get the other way around? Let's say we inserted the result into a new table. How can we can those commas separated columns to get the result as the accounts table? – Jonas T May 17 '12 at 12:23

score 0 · Answer 2 · edited May 17 '12 at 20:48

0

You need a standard join query:

select u.id,u.username,u.email,a.account from Users as u, Accounts as a where u.id=a.id;

edited May 17 '12 at 20:48

John Fisher

22,355
2
39
64

answered May 17 '12 at 12:15

Suyash

625
1
5
22

MYSQL request from two tables

2 Answers2