zimmer9
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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