Join command

[englund]

Server version: 5.0.87-community

 

I have two tables

 

A:

ID, FirstName, LastName

1, Frank, Smith

2, Bob, Jones

 

B:

GuestID, ID, GuestFirstName, GuestLastName

1, 1, Mary, Smith

2, 1, Ted, Smith

3, 1, Miss, Smith

 

If I use a simple JOIN

 

SELECT * FROM A LEFT JOIN B USING (RegID)

 

I get what you'd expect

 

1. 1, Frank, Smith, 1, Mary, Smith

2. 1, Frank, Smith, 2, Ted, Smith

3. 1, Frank, Smith, 3, Miss, Smith

4. 2, Bob, Jones

 

But I need my data is the following format

 

1. 1, Frank, Smith, Mary, Smith, Ted, Smith, Miss, Smith

2. 2, Bob, Jones

 

I tried a GROUP_CONCAT but I have very little experience with SQL and don't have a good grasp on how it works

 

SELECT *, GROUP_CONCAT(B.GuestFirstName,' ', B.GuestLastName SEPARATOR '- ') FROM A LEFT JOIN B ON A.RegID = B.RegID

 

Which gave me

 

1. 1, Frank, Smith, 1, 1, Mary, Smith, Mary Smith - Ted Smith - Miss Smith

 

(ignore the separator as I just used the "-" so it would be easier to see write my output here one the forum.)

 

So it's *kind* of doing what i want but I lost the

 

2. 2, Bob, Jones

 

data. Good chance I'm just barking up the wrong tree. Any help would be appreciated.

 

 

[englund]

SELECT *, GROUP_CONCAT(B.GuestFirstName,' ', B.GuestLastName SEPARATOR ', ') AS guestnames FROM (A LEFT JOIN B USING (RegID)) GROUP BY A.RegID

 

This appears to work and gives me

 

1. 1, Frank, Smith, Mary Smith, Ted Smith, Miss Smith

2. 2, Bob, Jones

 

"guestnames" makes a new column named guestnames with the concatenated values Mary Smith, Ted Smith, Miss Smith.

[fenway]

Sorry, what are you trying to achieve?