Jump to content


Photo

Help writing this query (joining three tables)


  • Please log in to reply
1 reply to this topic

#1 JohnJSal

JohnJSal
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 23 August 2006 - 04:11 PM

Hi everyone. I'm fairly comfortable with the basics of MySQL, but I'm a little stumped on how to join three tables. First off, let me explain the table structures (from a baseball database):

Master | playerID, nameFirst, nameLast
Schools | schoolID, schoolName
SchoolsPlayers | playerID, schoolID

So the first name in each row is the table name (Master, etc.) and the following names are entries in that table. What I'd like to do is write  a query that will display a player's name and the school he went to. This is what I've come up with so far, but I don't know if it works yet because my script isn't working properly (a separate issue).

select Master.nameFirst, Master.nameLast, Schools.schoolName
from Master, Schools, SchoolsPlayers
where Master.playerID = SchoolsPlayers.playerID and Schools.schoolID = SchoolsPlayers.schoolID

I did a little searching and came across the use of a left or right join as another way to work with more than two tables, but I've never used that before, so I don't know if it's necessary or how it works. It seems like a join is what I need, which the above is not, and I'd like to learn how to write one that uses three (or more) tables. (I can handle two!)

Thanks!
John

Quick note: This does work, but is it better to use joins in a case like this?

#2 fenway

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

Posted 23 August 2006 - 06:00 PM

If you know how to do it with 2, you can do it with N -- but try rewriting it in proper JOIN syntax, and the ON clauses will make life simpler for you.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users