0

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.

3

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.

  • That worked great, thanks so much. I did not find anything that would have described what I did wrong so I thank you again for including the explanation! – Tilting Code Apr 3 '18 at 17:40
  • @TiltingCode It's a common one, but yeah, not very discoverable. – Aaron Bertrand Apr 3 '18 at 17:42
  • Do you mean that you change your outer join to an inner join? – Seafish Oct 18 '18 at 18:52
  • @Seafish Nice catch (no pun intended) – Aaron Bertrand Oct 18 '18 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.