Jump to content


Photo

Help Please!! (Newbie) Selecting from multiple tables


  • Please log in to reply
5 replies to this topic

#1 john_grimsby

john_grimsby
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 11 September 2006 - 08:07 PM

Hello

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

RECORD LABEL (record_label_ref, record_label, country, subsidiary_of, label_info)
ARTIST (artist_ref, artist_name, artist_info)
RECORD (record_ref, cat_no, record_label_ref, label_display, artist_ref_a, song_a, artist_ref_b, 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!!!!




#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 08:36 PM

You'll need to JOIN in the the artist table twice to do this properly.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 john_grimsby

john_grimsby
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 11 September 2006 - 08:58 PM

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!!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 September 2006 - 09:16 PM

This should be close, but it's untested:

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 john_grimsby

john_grimsby
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 11 September 2006 - 09:51 PM

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

#6 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 12 September 2006 - 12:50 PM

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....
follow me on twitter @PHPsycho




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users