• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

MySQL Query

I have two tables; a and b. The following is the structure for said tables:

a
---------
a_id int
name

b
---------
b_id int
a_id int
b_name

The two tables have a 1 to many relationship; therefore for every one record in table a, there could be many records in table b that are related.

Here is the problem....

I am running the following SQL and getting the following results:

SELECT a.a_id, b.b_id FROM a INNER JOIN b ON a.a_id = b.a_id;

1,1
1,2
1,3
1,4
1,5
1,6
2,1
2,6
3,1
3,4
4,1
5,2

Instead of getting a multiple returned rows for each record in table b, is there a way to get them on a single line so the returned record sets look like this:

1,1,2,3,4,5,6
2,1,6
3,1,4
4,1
5,2
0
plecostomus
Asked:
plecostomus
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try:
SELECT a.a_id, group_concat(b.b_id)
 FROM a INNER JOIN b ON a.a_id = b.a_id
 GROUP BY a.a_id;

Open in new window

0
 
plecostomusAuthor Commented:
Worked perfectly
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now