CODEWITHSUNDEEP
sql-serversql-server-2008joinleft-join
RealSteel
RealSteel

Reputation: 1931

Inner Join and Left Join in same sql query

I'm trying to perform inner join and then left join in same sql query. But the left join is not working. It is not showing the null values

I've two tables EVENT_INVITATIONS and USERINFO which has 2 records when joined.

so, the join query goes like this :

select * from [UandMePROD].[dbo].EVENT_INVITATIONS EI
join [UandMePROD].[dbo].USERINFO UI on EI.USER_ID = UI.USER_ID and EI.EVENT_ID=11033

It gives out 2 records.

So, I'm performing a left join with another table CLIENT_CONTACTS which has only 1 matching record in it.

So, actually it should show the null value to the unmatched record. but it is not showing the second record. It is showing only 1 record which is matched(join)

My failed sql query :

select * from [UandMePROD].[dbo].EVENT_INVITATIONS EI
join [UandMePROD].[dbo].USERINFO UI on EI.USER_ID = UI.USER_ID 
 left  join CLIENT_CONTACTS CC  on UI.MOBILENO=CC.MOBILE_NUMBER  
where cc.CLIENT_ID=20111 and EI.EVENT_ID=11033

can you please tell me where I'm doing mistake? I need the 2 records.

Upvotes: 0

Views: 382

Answers (3)

M.Ali
M.Ali

Reputation: 69524

Since you are doing a left join, cc.CLIENT_ID is null for all the values which only exist in CLIENT_CONTACTS and your where clause Where cc.CLIENT_ID = 20111 converts your LEFT JOIN into INNER JOIN. Adding this filter in ON clause solves the issue.

select * 
from [UandMePROD].[dbo].EVENT_INVITATIONS EI
inner join [UandMePROD].[dbo].USERINFO UI on EI.[USER_ID] = UI.[USER_ID] 
 left join CLIENT_CONTACTS CC             on UI.MOBILENO = CC.MOBILE_NUMBER                 
                                         and cc.CLIENT_ID = 20111
where  EI.EVENT_ID=11033

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

Providing some suggestions,since it is not possible to say what is happening with out table data

Left join should show null values from output of first inner join eventhough there are no mobile number matches,so try removing where condition and see if you are getting any result

Upvotes: 0

Trouner
Trouner

Reputation: 316

You should not specify EI.EVENT_ID on WHERE clause. Those limit your results after the join. You should specify EI.EVENT_ID in an ON clause. 

select * from [UandMePROD].[dbo].EVENT_INVITATIONS EI join [UandMePROD].[dbo].USERINFO UI on EI.USER_ID = UI.USER_ID AND   (EI.EVENT_ID=11033 or EI.EVENT_ID is null) left  join CLIENT_CONTACTS CC  on UI.MOBILENO=CC.MOBILE_NUMBER  where cc.CLIENT_ID=20111

Upvotes: 0

Related Questions