Solved

MySQL Query

Posted on 2011-02-28
2
291 Views
Last Modified: 2012-06-22
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
Comment
Question by:plecostomus
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35001147
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
 

Author Closing Comment

by:plecostomus
ID: 35001826
Worked perfectly
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now