Joining a table to two fields 1

[RexJacobus]

I'm sure this is obvious but I just can't get my head around it. I have two tables, (PLAYERS contains ID, player_name etc, GAMES has player1_ID, p1_score, player2_ID, p2_score, date, etc).

I can't get the join written so that my output looks like
Federer 6 Roddick 4 01/01/2006.

I can only link PLAYERS.player_name to one of the IDs in the GAMES table.

thanks,

 

[AtomicWedgie]

select *
from players as playes1
, players as players2
, games
where players1.id = games.player1_id
and players2.id = games.player2_id

 

[Crowley16]

I'd recommend using the ansi syntax...

select * from
players as p1 inner join
players as p2 on p1.id = games.player1_id
...

--------------------
Procrastinate Now!

 

[Crowley16]

erm, that of course should be

select *
from
players inner join
games on players_id = games.player1_id ...

--------------------
Procrastinate Now!

 

[AtomicWedgie]

Gee,

just because Crowley16 rewrites my solution in ansi syntax he gets credits and me not :-(

 

[SQLSister]

Atomic Wedgie, while your solution will work in SQL 2000 it will not work in SQL 2005 and all code should be written the way Crowley suggested so that when the upgrade occurs things won't break. That's why his was the better solution.

Questions about posting. See faq183-874

 

[AtomicWedgie]

Damn,

doesn't that work anymore in SQL 2005, that sucks. I totally find it impossible to read the ansi syntax.

Well maybe the code was better then but the prinicpal idea of using aliasses was the clue.

Regards,

Atomic Wedgie

 

[monksnake]

Don't cry AtomicWedgie, your day in the spotlight will come.

<.