search for id in two fields and join the results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

search for id in two fields and join the results

Author

Topic

sq2
Starting Member

11 Posts

Posted - 2011-01-26 : 19:29:27

Hi, i almost have this query working, but not quite.

Trying to get a player's results and display who their opponent was.

SELECT players.player_name, player_results.* FROM (
SELECT * FROM games
WHERE (challenger_id = $player_id)
OR (challengee_id = $player_id)
) AS player_results INNER JOIN players ON player_results.challenger_id = players.player_id

this of course only gives me the name of the challenger, but i can't figure out how to get the name of the challengee

thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-01-26 : 22:00:37

it would help if you gave us the DDL for the games and players tables

elsasoft.org

sq2
Starting Member

11 Posts

Posted - 2011-01-27 : 04:12:52

players table:
player_id player_email player_name

games:
game_id challenger_id challengee_id winner_id

thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-27 : 04:52:23

Run the Below query!

select

Chalanger.player_name as Challenger
,Chalangee.player_name as Challengee
,Winner.player_name as Winner

From Games G
Inner Join Players Chalanger on Chalanger.player_id = G.challenger_id
Inner Join Players Chalangee on Chalangee.player_id = G.challengee_id
Inner Join Players Winner on Winner.player_id = G.winner_id

sq2
Starting Member

11 Posts

Posted - 2011-01-27 : 07:16:41

Great... slight modification and it works perfectly. thanks...

select
Challenger.player_name as Challenger,
Challengee.player_name as Challengee,
Winner.player_name as Winner,
G.date,
G.score
From (
Select * From games
WHERE (challenger_id = $player_id)
OR (challengee_id = $player_id)
) as G
Inner Join players Challenger on Challenger.player_id = G.challenger_id
Inner Join players Challengee on Challengee.player_id = G.challengee_id
Inner Join players Winner on Winner.player_id = G.winner_id

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-27 : 07:19:07

you are welcome!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources