Lovepreet Singh Batth Lovepreet Singh Batth - 7 months ago 4
SQL Question

Sql get data from two tables with a twist

I want two get data from two dables
users and data listed below

User table -->

id name time
1 first 2016-04-20 15:13:13
2 second 2016-04-20 15:13:13
3 third 2016-04-20 15:13:13
4 forth 2016-04-20 15:13:13
5 fifth 2016-04-20 15:13:13

Data table -->

id data user_id time
1 2000 1 2016-04-20 15:10:18
2 3000 2 2016-04-20 15:10:18
3 4000 3 2016-04-20 15:10:18
4 2000 4 2016-04-20 15:10:18
5 3000 5 2016-04-20 15:10:18


can join them easily by

select d.data
, u.name
from data d
left
join users u
on d.user_id = u.id
order
by d.data DESC


its gives -->

data name
4000 third
3000 fifth
3000 second
2000 forth
2000 first


But i want it like only with sql query (can be done with a muti dim array)

any way i want only with query

data name name
2000 first forth
3000 second fifth
4000 third


Plz guide me

Answer

Try this

 select data.data,GROUP_CONCAT(users.name) from data left join users on   
 data.user_id=users.id GROUP BY data.data order by data.data DESC