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.