Menu
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
RequestStatus
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.
804
In the WHERE clause, you can specify left and right outer joins only. To outer join tables TABLE1 and TABLE2 and return non-matching rows from TABLE1 (a left outer join), specify TABLE1 LEFT OUTER JOIN TABLE2 in the FROM clause or apply the (+) operator to all joining columns from TABLE2 in the WHERE clause.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.
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.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
