outer self join with where 1

[simian336]

Any idea why this would not work.

select a.object_id,a.column_id , a.name,
b.object_id,b.column_id, b.name
from sys.columns a
left outer join sys.columns b
on a.column_id = b.column_id
where a.object_id=1109578991
and b.object_id=2130106629

a.object_id=1109578991 has 40 records
and
b.object_id=2130106629 has 39 records

But it is showing 39 matched records and no null.

Shouldn't it match all 40 with 1 on the right being null.

Thanks

Simi

 

[r937]

your WHERE condition on the "b" table effectively removes the unmatched row

try it like this --

SELECT a.object_id
     , a.column_id 
     , a.name
     , b.object_id
     , b.column_id
     , b.name
  FROM sys.columns AS a
LEFT OUTER 
  JOIN sys.columns AS b  
    ON b.column_id = a.column_id
   [red]AND[/red] b.object_id = 2130106629
 WHERE a.object_id = 1109578991

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[simian336]

Thanks Rudy

Worked perfectly...

Simi