Reputation: 95950

Left Join Issue

Am trying to do a left join along with a condition. Say this is my table:

a     b     c
-------------------------
1     1     NULL
3     3     something

and my query is

select * from x left join y on x.a = y.b

The problem is that I don't want c to be "something" so when i add

select * from x left join y on x.a = y.b where y.c <> 'something'

it displays 0 rows. it should actually display

a     b     c
-------------------------
1     1     NULL

Upvotes: 2

Answers (3)

Reputation: 21533

Old reply, but normally for this the easiest solution is to put the check in the ON clause:-

SELECT * 
FROM x 
LEFT OUTER JOIN y 
ONx.a = y.b 
AND y.c <> 'something'

Upvotes: 0

Reputation: 18257

select * 
from x left join y on x.a = y.b 
WHERE y.c IS NULL OR y.c <> 'something'

Upvotes: 2

Reputation: 15222

I think you ment:


SELECT *
FROM x
LEFT JOIN y ON x.a = y.b
WHERE y.c <> 'something'

Upvotes: 1