Trying query for missing criteria in a dataset

Other SQL Server Topics
1

Hi,

I'm attempting to write a query to look for missing data in a group of records..

When I run this query:

SELECT oh.Order_Number AS Order_Number,
cc.Customer_Name AS Customer_Name,
tc.Terms_Code AS Terms_Code,
ad.Address_Type AS Address_Type 

FROM
dbo.Order_Header oh LEFT OUTER JOIN
dbo.Customer_Code cc ON oh.Customer_Code = cc.Customer_Code LEFT OUTER JOIN 
dbo.Terms_Code tc ON cc.Terms_Code = tc.Terms_Code LEFT OUTER JOIN
dbo.Address ad ON cc.Customer_Code_ID = ad.Customer_Code_ID    

WHERE
cc.Customer_Name IN ('BSM', 'BSA')

I get these results

12345, BSM, 50% Down, Billing
12345, BSM, 50% Down, Shared
12345, BSM, 50% Down, Office
12345, BSM, 50% Down, Shipping
12346, BSM, 50% Down, Billing
12346, BSM, 50% Down, Shared
12346, BSM, 50% Down, Office
12346, BSM, 50% Down, Shipping
12347, BSA, PrePay, Shipping
12347, BSA, PrePay, Shared
12347, BSA, PrePay, Office
12348, BSA, PrePay, Shipping
12348, BSA, PrePay, Shared
12348, BSA, PrePay, Office

I'm attempting to find Order Numbers that have a customer that does not have a "Billing Address". So in a corrected query I would only find 12347 and 12348.

I'm finding it extremely hard to search for data that doesn't exist. Do I need to do a UNION to group the data? I'm not certain how to go about looking for a group of data that doesn't include a criteria.

Any help would be greatly appreciated.

2

Try this:

SELECT oh.Order_Number AS Order_Number,
cc.Customer_Name AS Customer_Name,
tc.Terms_Code AS Terms_Code,
ad.Address_Type AS Address_Type 

FROM
dbo.Order_Header oh LEFT OUTER JOIN
dbo.Customer_Code cc ON oh.Customer_Code = cc.Customer_Code LEFT OUTER JOIN 
dbo.Terms_Code tc ON cc.Terms_Code = tc.Terms_Code LEFT OUTER JOIN
dbo.Address ad ON cc.Customer_Code_ID = ad.Customer_Code_ID

and AD.Address_Type = 'Billing'

WHERE
cc.Customer_Name IN ('BSM', 'BSA')

and ad.Address_Type is null

3

Thanks Mike!!!!