Sql-server – Pull valid row from table even when joining table has no valid data

sql servert-sql

I have two tables with a 0 to many relationship. Table A holds the one record where Table B can hold 0 to many records. There is a status field in both tables which can be equal to 'U' or 'D'.

I want to be able to pull the one record from TableA where status <> 'D' even where there are no related records in TableB or when all of TableB's related records are equal to 'D'.

In the below code TableA has a good record and TableB has two related records but their Status = 'D'. So, I still want to pull the data from TableA and blank\null fields from TableB:

SELECT A.Field1, 
       A.Field2, 
       A.KeyField1, 
       B.Field1, 
       B.Field2, 
FROM TableA A 
LEFT OUTER JOIN TableB B ON A.KeyField1 = B.KeyField1 
AND A.KeyField2 = B.KeyField2 
WHERE A.KeyField = '11111' 
AND A.Status <> 'D' 
AND B.Status <> 'D' 
ORDER BY A.KeyField

When I Select from these tables individually such as:

Select *
From TableA
WHERE KeyField = '11111' 

Select *
From TableB
WHERE KeyField = '11111'

I get the results I expect, TableA's Status field = 'U' and TableB's Status field = 'D'. I know the data is there and is equal to what it should be so, I know there's something wrong with my query.

Best Answer

Try rearranging your conditions like so:

...
LEFT OUTER JOIN TableB B ON A.KeyField1 = B.KeyField1 
AND A.KeyField2 = B.KeyField2 
AND B.Status <> 'D' 
WHERE A.KeyField = '11111' 
AND A.Status <> 'D' 
...

When you put an outer table in the WHERE clause, you force SQL Server to filter only to rows that exist and where that value can be explicitly evaluated, in other words you change your outer join to an inner join.

Related Question