CODEWITHSUNDEEP
sqlleft-join
Manju
Manju

Reputation: 2640

Issue with my Left outer join query

Here are my tables,

enter image description here

I'm trying to fetch the list of free bets with the details if the user has placed his bet or not. This is the query i have written to fetch the details for the same,

select DISTINCT tbl_CreateFreeBet.FreeBetID, 
        tbl_CreateFreeBet.FreeBetDescription, 
        tbl_CreateFreeBet.FreeBetAmount, 
        tbl_CreateFreeBet.TournamentID, 
        tbl_CreateFreeBet.MatchID, 
        tbl_UserFreeBets.UserForYes, 
        tbl_UserFreeBets.UserForNo, 
        tbl_UserFreeBets.UserForNoBets  
from tbl_CreateFreeBet left outer join tbl_UserFreeBets
on tbl_CreateFreeBet.MatchID = 1 and
tbl_CreateFreeBet.MatchID = tbl_UserFreeBets.MatchID and 
tbl_CreateFreeBet.FreeBetID = tbl_UserFreeBets.FreeBetID and 
tbl_CreateFreeBet.TournamentID = tbl_UserFreeBets.TournamentID and 
(tbl_UserFreeBets.UserForYes = 'User2' or tbl_UserFreeBets.UserForNo = 
'User2')

This is working fine, when there is a data in tbl_CreateFreeBet table for the MatchID. But if there is no data, then this query is not returning the expected result.

For example: With tbl_CreateFreeBet.MatchID = 1, I need to get all the free bets of matchID = 1, with the details of the passed in user, if has bet on 'yes' or 'no'. This comes up fine, as there is data for MatchId = 1 in tbl_CreateFreeBet.

But, it fails, when the tbl_CreateFreeBet.MatchID = 2 input is passed. Here there is no free bet created for the MatchID = 2. But still it returns me the result for MatchID=1.

Please share the query if one is aware of what changes need to be done for my query. Thank you.

Upvotes: 1

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Conditions on the first table in a LEFT JOIN should be in the WHERE clause. I think you intend this logic:

select cfb.FreeBetID, cfb.FreeBetDescription, cfb.FreeBetAmount, 
       cfb.TournamentID, cfb.MatchID, 
       ufb.UserForYes, ufb.UserForNo, ufb.UserForNoBets  
from tbl_CreateFreeBet cfb left outer join
     tbl_UserFreeBets ufb
     on cfb.MatchID = ufb.MatchID and 
        cfb.FreeBetID = ufb.FreeBetID and 
        cfb.TournamentID = ufb.TournamentID and 
        (ufb.UserForYes = 'User2' or ufb.UserForNo = 'User2')
where cfb.MatchId = 1

Upvotes: 2

Related Questions