[RESOLVED] SQL Query Help

[lintz]

I've got 3 tables which I've outlined below. What I'm trying to display is the 'UserName' from table1 where all the 'FriendName' for each 'UserID' in table2 don't appear in table3 and GameID in table 2 = 5.

So the end result would display 'Ben'. 'Bill' wouldn't be displayed because even though Luke doesn't appear in table 3 for GameID 5, John does.


Table1
ID, UserName
1, Bill
2, Ben


Table2
UserID, FriendName, GameID
1, John, 5
1, Luke, 5
1, Mark, 6
1, Steve, 6
2, Phil, 5
2, Grant, 5
2, Sean, 6
2, Karl, 6

Table3
FriendName, GameID
John, 5
Steve, 5
Gary, 5
Glen, 6
Cameron, 6


Any help would be appreciated.

[brucevde]

You will need to use the Exists and In clauses against SubQueries.

Select Id, Username
From Table1
Where Exists (Select UserId From Table2 Where UserId = Table1.Id And GameId = 5) And
Id Not In (Select UserId From Table2 T2 Inner Join Table3 T3 On T2.FriendName = T3.FriendName And T2.GameId = 5)

[lintz]

brucevde, your query seems to only work when GameID =5 (Ben is only record returned), however if you enter GameID = 6 no records are returned when actually it should be 2 (Bill and Ben) as neither of their 'FriendName' appear in Table 3 for GameID = 6.

[brucevde]

Change the second subquery to

Select UserId From #Table2 T2 Inner Join #Table3 T3 On T2.FriendName = T3.FriendName And T2.GameId = T3.GameId Where T2.GameId = 5

[lintz]

Thanks brucevde, that seems to do the trick