Jump to content


Photo

MySQL join query


  • Please log in to reply
2 replies to this topic

#1 Robin M

Robin M
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationUK

Posted 13 January 2003 - 05:40 PM

I have bought two MySQL books + two on PHP, spent most of yesterday looking round the net but still can\'t answer this apparently simple query.

I have three tables
1 Players (I do not know if this is relevant)
2 Data
3 Clubs

Table 2 contains a fromClub and a toClub field (both smallint)
Table 3 has just ID field (smallint) and a name

I would like to display the name fields in a row instead of the IDs, but can\'t get them to do so using joins or where statements.

e.g
SELECT * FROM player, data, clubs
WHERE player.playerID = data.playerID
AND data.fromClub = clubs.ClubID

lists the fromClub name in both fromClub and toClub columns

So instead of
---------------------------------------------
Name Birthday FromClub To Club
--------------------------------------------
Jones 1953-04-01 12 14
etc

I want
---------------------------------------------
Name Birthday FromClub To Club
--------------------------------------------
Jones 1953-04-01 Bury Burnley
etc

Thanks for any help.

Robin

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 14 January 2003 - 11:07 AM


SELECT player.name, c1.name, c2.name 

 FROM player, data, clubs c1, clubs c2

 WHERE (player.playerID = data.playerID) 

AND 

 data.fromClub = c1.ClubID 

AND

 data.toClub = c2.ClubID


I think this will work. You need to select TWO club names per row, hence you need to select from two copies of the clubs table.

Try it,

P., denmark
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 Robin M

Robin M
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationUK

Posted 15 January 2003 - 02:17 PM

Hi Palle

That works perfectly thank-you.

All my time spent trying different joins and yet it was aliases that solve it.

I am really grateful

Robin




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users