Select Query from 3 tables with foreign keys

Question

I Have 3 Tables with foreign keys to each other. I want to write a SQL Server Stored Procedure to select records from one of them.

Now, let's suppose that i want all the Winner records referring to the Player records referring to The Game with the ID=2, how can i proceed?

Thank you.

Obsequent

You would apply an INNER JOIN with a filtering where clause; codinghorror.com/blog/2007/10/… – Highcolored

Answer 1

you have specified all the Winner records So that i have used the left join for player and game. But the Overall code works according to the where condition.

Try This,

select w.* from Winner w
left Join Player p on p.ID_player = w.player_FK
left join Game g on g.ID_game = p.Game_FK
where  Game.ID_game = 2

Clipfed

@Clipfed can you please tell me how to perform same above operation with Group By like select all latest winner record for all Games. – Scalable

Answer 2

You need to use a SELECT and INNER JOIN then to filter on GameID 2 you can use a WHERE clause.

SELECT ID_Winner, Name, Lastname, Player_FK
FROM Winner
INNER JOIN Player on Player.ID_Pplayer = Winner.Player_FK
INNER JOIN Game ON Game.ID_game = Player.Game_FK
WHERE Game.ID_game = 2

Spancake

when i do that in my method which returns a list<WinnerDetails> (the class for winner records that calls the stored procedure) i get : System.IndexOutOfRangeException: ID_player – Obsequent

@SlimaneAgouram - this is a different question. You should accept an answer here (if it helped you with the original problem) and then post a new question with the details of your problem, as well as a snippet of the code which is causing the Exception. – Spancake

Your answer helped me with my problem, only thing left is to check that i have everything in place to make it work. Thank You. – Obsequent