Jump to content

Archived

This topic is now archived and is closed to further replies.

Robin M

MySQL join query

Recommended Posts

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

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.