CODEWITHSUNDEEP
sql-server
Eyal
Eyal

Reputation: 4763

Sql LEFT OUTER JOIN with WHERE clause

I have two tables:

Request:

 RequestID | Msg
----------------
    5  | abc
    6  | def
    7  | ghi
    8  | jkl

RequestStatus:

RequestStatusID | RequestID |StatusID
-------------------------------------
        1             5         1
        2             8         2

I need all the records from table Request except when StatusID = 2. (requestID=8 should be filter-out)

I am using LEFT OUTER JOIN to recieve the records from table Request but when I am adding Where clause (Where StatusID = 1) of course it does not work.

Upvotes: 12

Views: 48108

Answers (6)

Tommaso
Tommaso

Reputation: 351

In SQL Server 2017 you can put AND after ON in this way

SELECT * FROM Request
LEFT OUTER JOIN RequestStatus ON Request.RequestID = RequestStatus.RequestID
AND RequestStatus.StatusID <> 2
WHERE Request.RequestID IS NOT NULL

Upvotes: 4

SqlZim
SqlZim

Reputation: 38023

SqlFiddle

This answer assumes you just want the RequestId & Msg from the Request Table where there is not a record in the RequestStatus table with that RequestId and a StatusId of 2.

You won't get extra records for Requests with multiple RequestStatus records using this query either (vs Left join).

Using the not exists clause is faster than Except, Not In, Outer Apply, etc in this article by Aaron Bertrand - Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

select r.RequestId, r.Msg
  from Request r
  where not exists (
    select 1 
    from RequestStatus rs 
    where rs.StatusId = 2
      and rs.RequestId = r.RequestId
    )

Upvotes: 0

craigster
craigster

Reputation: 124

You need use an NOT IN Query Statement like this

Select * 
From Request 
Where RequestID not in 
    (Select RequestID 
    From RequestStatus 
    Where StatusID = 2)

or

Select * 
From Request r 
left join requestStatus rs on r.requestID = rs.requestID 
Where r.RequestID not in 
    (Select RequestID 
    From RequestStatus 
    Where StatusID = 2)

Upvotes: 1

Andrew
Andrew

Reputation: 8693

Move the constraint to your on clause.

select *
from request r
left join requestStatus rs
on r.requestID = rs.requestID
--and status_id = 1
and status_id <> 2

What's happening to you is that the outer join is performed first. Any rows coming from the outer join that don't have matches will have nulls in all the columns. Then your where clause is applied, but since 1 <> null, it's not going to work like you want it to.

EDIT: Changed on clause based on Piyush's comment.

Upvotes: 22

Eduard Uta
Eduard Uta

Reputation: 2607

The answer is pretty simple, use left join but filter on the rows that have StatusId different than 2, like so:

select *
from request r
left join requestStatus rs
on r.requestID = rs.requestID
where rs.StatusId <> 2 or rs.StatusId IS NULL

EDIT: added or rs.StatusId IS NULL additional condition to also include rows in requestStatus table that don't have a match in request table.

Upvotes: 1

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

Try this

SELECT *
FROM Request R
  LEFT JOIN RequestStatus RS ON R.RequestID  =  RS.RequestID   
  WHERE RS.StatusID <> 2 OR RS.RequestID IS NULL

SQL FIDDLE

Upvotes: 3

Related Questions