A where on an 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

SQL Server 2008 Forums

Transact-SQL (2008)

A where on an outer join

Author

Topic

Heinduplessis
Starting Member

23 Posts

Posted - 2011-12-08 : 09:09:03

This is a problem I have every now and then and I've never been able to find an elegant solution.

When I do a left outer join, sql returns all the rows of the left table with nulls not matching the right table.

Say I want to filter the left table with a where, it still returns ALL the rows of the left.

e.g.

select c.CustomerID, cs.CustomerStatusName
from Customer c
left outer join Customer cs on c.CustomerStatusID=cs.CustomerStatusID
where c.CustomerID=1

This returns the entire customer table.

Any way to do this without doing multiple sub queries?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:11:51

it wont return all rows from customer table but it will return only rows from table with customerid=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Heinduplessis
Starting Member

23 Posts

Posted - 2011-12-08 : 09:28:06

Wow, I had an and in stead of a where!! Sorry for the dumb question.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:33:44

it happens to everyone

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources