Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to get a result set of non matching records using SQL Server 2005?

I'm working with SQL Server 2005:

If the following records match:

SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number]
FROM dbo.tblCustomersNew AS C
LEFT JOIN dbo.tblStatesAll AS S ON S.StateFS = C.ResStateCode  
INNER JOIN dbo.tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber  
AND C.OfficeNumber = P.OfficeNumber

How would you write a SQL Statement for the records that don't match
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zimmer9

ASKER

My humble apologies:

Let me restate my query:

There are actually 2 queries because of the field S.FallCycle:

I tried a record count of the table dbo.tblProductsNew
and it doesn't match the record count I get from
Query 1 + Query 2 as follows:
So I am trying to reconcile my difference.

Query 1)
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number
FROM dbo.tblCustomersNew AS C
LEFT JOIN dbo.tblStatesAll AS S ON S.StateFS = C.ResStateCode  
INNER JOIN dbo.tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber
WHERE (S.FallCycle= 1)
-----------------------------------------------------------------------------------------------------------
Query 2)
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number]
FROM dbo.tblCustomersNew AS C
LEFT JOIN dbo.tblStatesAll AS S ON S.StateFS = C.ResStateCode   -- Use LEFT JOIN and check for NULL in StateFS for non-matching values.
INNER JOIN dbo.tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber  AND C.OfficeNumber = P.OfficeNumber
WHERE (S.FallCycle= 0)

UNION ALL

SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number]
FROM tblCustomersNew AS C
LEFT JOIN tblStatesAll AS S ON S.StateFS = C.ResStateCode  
INNER JOIN tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber
WHERE S.StateFS IS NULL
Avatar of zimmer9

ASKER

correction:

UNION ALL

SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number]
FROM tblCustomersNew AS C
LEFT JOIN tblStatesAll AS S ON S.StateFS = C.ResStateCode  
INNER JOIN tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber AND
C.OfficeNumber = P.OfficeNumber
WHERE S.StateFS IS NULL
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial