MySQL join query

[Robin M]

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

[pallevillesen]

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

[Robin M]

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