CODEWITHSUNDEEP
phpsqlarraysjoin
mevr
mevr

Reputation: 1125

Join and group by ouput as single array

Is there any way to join tables and group results as single array using single query using mysql and sql server

Suppose i have two tables user and user_jobs

user:

user_id   username
-------  ----------
 1        user1
 2        user2

user_jobs:

user_id  job   place designation
 ------  ---- ------ ---

1         job1    p1     d1
1         job2    p2     d2
1         job3    p3     d3

select * from user join user_jobs on user.user_id = user_jobs.user_id

I will get three rows for the query result.Is there anyway to join these tables and output the result as one array?

Expected Output:-

[
  username:user1,
  user_id:1,
  job:array(jobs),
  place:array(place),
  designation:array(designation)
]

Upvotes: 0

Views: 56

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

You can try using GROUP_CONCAT like this and turn that result (a comma separated list into array) using php explode.

select user.username,user.user_id,
       GROUP_CONCAT(job) as jobs,
       GROUP_CONCAT(place) as place,
       GROUP_CONCAT(designation) as designation 
from user join user_jobs on user.user_id = user_jobs.user_id
GROUP BY user_id,user_name

UDPATE: If thought this question had a mysql tag... I guess not If you're using SQL Server, You can try 

SELECT user.username,user.user_id,
       job, place, designation
from user join user_jobs on user.user_id = user_jobs.user_id
ORDER BY user.username,user.user_id

and then in php have it loop through the returned resultset building the arrays for job,place,designation detecting when a user_id changes to create a new user

Upvotes: 1

Related Questions