MySQL 3 Table join question...

[eluser]f244[/eluser]
[quote author="port22" date="1267800466"]It changes the query, but just use the join table (artists_cds) as the starting point.

So, you would do a join much like this:

select * from
artists_cds join artists
join catalogue
join format

Which will produce a detailed table of everything. If you want a specific artist, catalogue or format, you can just throw in where clauses as necessary.[/quote]

Many thanks for the info and suggestions. I've re-designed my DB as you recommended and after some experimentation I'm starting to see the advantages of this approach. I have one further question though, one thing I can't quite figure out.

Let's say I want to get the details for a single item in the catalogue (in this case item 5 which has 3 artists associated with it), I could use a query like:

Code:

select catalogue.title,artists.name from
artists_cds
join artists on artists.id=artists_cds.artid
join catalogue on catalogue.id=artists_cds.catid
where catalogue.id=5

I get a result back with 3 rows, one for each artist:

Row 1 -> Title, Artist(1)
Row 2 -> Title, Artist(2)
Row 3 -> Title, Artist(3)

Is there a way to modify the query so I can have the results as a single row with all the artist names either in separate fields or in an array or something like that?

I have a similar problem with the overall catalogue, I can work out how to get a list of everything but I've always got this problem of multiple rows for a catalogue item where there are multiple artists associated with that item.

Thanks for any help or tips!