Join question 1

[brownfox]

I have two tables. Names:

[u]ID   |First|Last[/u]
1    |Joe  |Blogs
2    |Ann  |Smith

Colors:

[u]Foreign_ID|Color[/u]
1         |Blue
1         |Red
1         |Green
2         |Red

Using a table join how do I get a record that's like this:

Joe Bloggs Blue Red Green

"SELECT names.*,Color from names INNER JOIN Colors ON (Colors.Foreign_id = names.ID)" gives me three rows with duplicate names etc.
Anyone know?
Thanks in advance!

 

[TonyGroves]

Can't be done. What you are looking for is a non-relational result set with an indeterminate number of columns, and SQL is just not designed for that purpose.

 

[brownfox]

OK Thanks, how do most people deal with this problem. I know this answer must deal with PHP but what is the general approach to this situation?

 

[lgarner]

It's not language-specific. Most likely, you would retrieve the recordset, print the name once, and loop through the results to print the colors. Kind of like:

print result[name]
foreach result
print result[color]

 

[r937]

actually it is quite possible, and dead simple, too

just use the GROUP_CONCAT function

select Names.First
     , Names.Last
     , group_concat(Colors.Color 
            separator ' ') as colors
  from Names 
inner
  join Colors 
    on Names.ID 
     = Colors.Foreign_id

mind you, you get all the colors in one column, so if you wanted a separate column for each one, then you're outta luck



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[r937]

aaargh, i forgot the most important part of that query, the GROUP BY clause

select Names.First
     , Names.Last
     , group_concat(Colors.Color 
            separator ' ') as colors
  from Names 
inner
  join Colors 
    on Names.ID 
     = Colors.Foreign_id
group
    by Names.First
     , Names.Last

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[brownfox]

GROUP_CONCAT mySQL 4.1 and upwards. I've got 4.0.23

 

[r937]

yeah, but you didn't say that at the outset

considering that 4.1 has been in production status since october, i now assume people will speak up if they are on an older version

this was not the case, say, a year ago, when 4.1 was still in beta, so i always took the time to point out that a certain feature (e.g. subqueries, GROUP_CONCAT, etc.) was available only in the beta release

nowadays i always point out when something that will solve someone's problem is available in 5.0, because that's still in beta

but i think it's fair to assume 4.1 today unless the person says otherwise

you will have to go with your original query and post-process the result rows in your scripting language

make sure you have an ORDER BY on lastname,firstname so that you can detect name changes



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)