Solved

MySQL Query

Posted on 2011-02-28
2
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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