Left Outer Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left Outer Join

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-06-25 : 18:58:56

Hi,
I'm running query to get the result.


select count(*) from keys k
left join products p on p.id = k.product_id
left join vendors v on v.id = k.vendor_id
where p.deleted = 0 and v.deleted = 0

Unfortunately, the above query does not return me the data in the keys table which is having vendor_id as null. I need this query to return the data in keys table for which vendor_id is null too. How can I achieve it by modifying the above query?

Note: Due to legacy reasons, I cannot change the left join at all!

Thanks!

singularity
Posting Yak Master

153 Posts

Posted - 2010-06-25 : 19:19:52

[code]
select count(*)
from keys k
left join products p on p.id = k.product_id and
p.deleted = 0
left join vendors v on v.id = k.vendor_id and
v.deleted = 0
[/code]

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-06-25 : 19:24:06

Hi,
Thanks for the reply. Unfortunately, the query is returning me all the records in keys table. I need only those records in keys table which are having p.deleted = 0 & v.deleted = 0 & also those which are having vendor_id as null

Thanks!

singularity
Posting Yak Master

153 Posts

Posted - 2010-06-25 : 22:39:08

Can you post some sample data and expected output?

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-06-26 : 21:16:03

Try this instead:

select count(*) from keys k
left outer join products p on k.product_id = p.id
left outer join vendors v on k.vendor_id = v.id

where v.deleted = 0 or is null
and p.deleted = 0

Amber-

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-28 : 03:33:27

I dont know what you are trying to do...
what is v.deleted = 0 condition ??

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-28 : 04:55:34

quote:
Originally posted by Amber_Deslaurier

Try this instead:

Watch the brackets on the where clause

select count(*) from keys k
left outer join products p on k.product_id = p.id
left outer join vendors v on k.vendor_id = v.id

where (v.deleted = 0 or v.deleted is null)
and p.deleted = 0

--
Gail Shaw
SQL Server MVP

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-07-02 : 17:54:29

Thanks! LOL I did miss that! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources