CODEWITHSUNDEEP
sqljoinleft-join
Myo Min Lin
Myo Min Lin

Reputation: 35

Can't Retrieve Exact Result with LEFT JOIN

I have three tables with the following structures:

User Table

UserID  Name  Age
------  ----  ---
   001    AA   23
   002    BB   25
   003    CC   30
   004    DD   24
   005    EE   23

temp_User Table

UserID  Name  Age
------  ----  ---
   001    AA   23
   002    BB   25
   004    DD   24
   005    EE   23
   007    GG   23
   009    HH   28

ExceptionUsers Table

UserID  Status
------  --------
   021    Active
   002  Inactive
   004    Active
   010    Active
   012  Inactive

I used the following query to retrieve my result:

Query

select
     A.Name
    ,B.Name
    ,A.Age
    ,B.Age
from User A 
inner join temp_User B
    on A.UserID = B.UserID
left join ExceptionUsers C
    on A.UserID = C.UserID
    and C.Status = 'Inactive'
order by
    A.Name

Result

001  AA  23
002  BB  25
005  EE  23

But the result includes users who are 'Active'.

001  AA  23
002  BB  25
004  DD  24
005  EE  23

How can I try the query to get my result?

Upvotes: 0

Views: 46

Answers (4)

jarlh
jarlh

Reputation: 44795

I'd move the LEFT JOIN logic to the WHERE clause, to a NOT EXISTS.

select A.Name, B.Name, A.Age, B.Age
from User A 
inner join temp_User B
on A.UserID = B.UserID
where not exists (select * from ExceptionUsers C
                  where A.UserID = C.UserID
                  and C.Status = 'active')
order by A.Name

Upvotes: 0

MKONDKARI
MKONDKARI

Reputation: 9

you can use the below query to get the list of active user with their details.

select * from temp_User where UserID in (select UserID from ExceptionUsers where Status = 'Inactive')

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

Left join does not filter the data provided on joining condition.

So you may move the joining condition and C.Status = 'Inactive' to where clause and it treats as inner join

select A.Name, B.Name, A.Age, B.Age
from User A 
inner join temp_User B
on A.UserID = B.UserID
left join ExceptionUsers C
on A.UserID = C.UserID
where
C.Status = 'Inactive'
order by A.Name

Upvotes: 0

idstam
idstam

Reputation: 2878

Move C.Status = 'Inactive' to the where clause.

Upvotes: 1

Related Questions