CODEWITHSUNDEEP
sqljoin
Shawn
Shawn

Reputation: 34229

sql left join and duplicates in result

Say I have 2 tables, A and B, each A entity can possibly have multiple B entities, in one case if I want to get all B's of some certain A's, I might do it with a simple left join 

select A.id aid,B.id bid from A
left join B on B.aid = A.id
where A.id = 1

and it will return a result set like 

aid   bid
1     1
1     2
1     3

As you can see for the first column, all those 1's are kinda duplicates. Is it possible to modify the SQL statement to let him return a result like

aid    bid
 1      1,2,3

in other words to link all the bid's together as one entity?

Also what if there's another table C, and each A can have multiple C's, how to I make the SQL return a result set like

 aid    bid    cid
  1     1,2,3   1,2

instead of 

 aid     bid    cid
  1       1      1
  1       2      1
  1       3      1
  1       1      2
  1       2      2
  1       3      2

Thank you very much!

Upvotes: 0

Views: 1742

Answers (2)

ajma
ajma

Reputation: 12206

Try using the COALESCE function.

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Upvotes: 0

benlumley
benlumley

Reputation: 11382

What DBMS are you using?

I can't speak for others, but in MySQL, starting from 4.1, you can use GROUP_CONCAT

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

EG: 

select A.id aid, GROUP_CONCAT(DISTINCT B.id) bid from A
left join B on B.aid = A.id
where A.id = 1
GROUP BY a.id

Upvotes: 3

Related Questions