CODEWITHSUNDEEP
mysqlsqlselect
tomalex
tomalex

Reputation: 1271

Join 3 tables to get single row

I'm trying to join multiple table to get single row result for each id. This result will be send to angular UI

I have 3 tables user, friends and trip A user can have multiple friends but one trip

I like to get all details corresponding to a user in one row, probably friends as field like an array?

This is how my table looks.

http://sqlfiddle.com/#!9/0879d/2

https://gist.github.com/tomalex0/9dee4fff85583732e7d0

Upvotes: 1

Views: 190

Answers (1)

Mureinik
Mureinik

Reputation: 311163

group_concat should do the trick for you:

SELECT    u.*, t.*, friendlist
FROM      user u
LEFT JOIN trip t ON u.id = t.user_id
LEFT JOIN (SELECT   user_id, GROUP_CONCAT(CONCAT (name, '- ', email)) friendlist
           FROM     friends
           GROUP BY user_id) f ON f.user_id = u.id

Upvotes: 1

Related Questions