Help Please!! (Newbie) Selecting from multiple tables

[john_grimsby]

Hello

I am trying to build a reggae 7" single discography database, with three tables

RECORD LABEL ([u]record_label_ref[/u], record_label, country, subsidiary_of, label_info)
ARTIST ([u]artist_ref[/u], artist_name, artist_info)
RECORD ([u]record_ref[/u], cat_no, [i]record_label_ref[/i], label_display, [i]artist_ref_a[/i], song_a, [i]artist_ref_b[/i], song_b, year)

As you can see, each record has two artists, one on each side. I want a query which displays the information for each record like so (I've omitted a few columns for simplicty):

PAMA 1 (A) ARTIST A Song A c/w (2) ARTIST B Song B (1960)

So far I have got the query:

SELECT record_label.record_label, record.cat_no, label_display, artist_ref_a, artist.artist_name, song_a, composer_a, producer_a, arranger_a, artist_ref_b, artist.artist_name, song_b, composer_b, producer_b, arranger_b, year, est_value, rarity_rating, rec_info
FROM record, record_label, artist
WHERE record.record_label_ref = $record_label_ref
AND record.artist_ref_a = artist.artist_ref
AND record.artist_ref_b = artist.artist_ref
AND record.record_label_ref = record_label.record_label_ref;

However, this query will only display records where artist_ref_a is the same as artist_ref_b.

CAN ANYONE PLEASE HELP - IT'S DRIVNG ME CRAZY!!!!

[fenway]

You'll need to JOIN in the the artist table twice to do this properly.

[john_grimsby]

Cheers mate, can you give me a quick pointer my heads spinning and i can't think straight - it took me about 10 hours to get that first query!!

[fenway]

This should be close, but it's untested:

[code]
SELECT record_label.record_label, record.cat_no, label_display, artist_ref_a, artistA.artist_name, song_a, composer_a, producer_a, arranger_a, artist_ref_b, artistB.artist_name, song_b, composer_b, producer_b, arranger_b, year, est_value, rarity_rating, rec_info
FROM record
INNER JOIN record_label ON ( record.record_label_ref = record_label.record_label_ref )
INNER JOIN artist AS artistA ON ( record.artist_ref_a = artistA.artist_ref )
INNER JOIN artist AS artistB ON ( record.artist_ref_b = artistB.artist_ref )
WHERE record.record_label_ref = $record_label_ref
[/code]

[john_grimsby]

Thanks - IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Peace man, I hope you have a good day, nice to know there's people who help others for no financial gain! :D

[ToonMariner]

Aye up cod head!!!!

(that is if you from GY UK not GY Canada!)

You may guess from my screen name that I am an exile in geordie land!

Welcome to phpfreaks anyway matey....