Solved

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

Posted on 2013-11-08
6
521 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:zimmer9
6 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
ID: 39634332
try this

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.StateFS Is Null
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 125 total points
ID: 39634334
Try this:

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   
LEFT JOIN dbo.tblProductsNew AS P ON C.CustomerNumber = P.CustomerNumber  
AND C.OfficeNumber = P.OfficeNumber 
WHERE P.OfficerNumber Is NULL AND p.CustomerNumber  IS NULL

Open in new window

0
 

Author Comment

by:zimmer9
ID: 39634393
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:zimmer9
ID: 39634406
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
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39636465
>>record count ... dbo.tblProductsNew ... doesn't match ... count ... from Query 1 + Query 2
well query 1 and query 2 involve 2 other tables, and you are use new customers as the from table, so any records in new products that hasn't yet got a customer won't be included. You also have 2 conditions on one field (FallCycle) and one on another field (StateFS) so if any of those conditions aren't met then they won't be counted either.

Why not start with something like this? It assumes there is an ID field on tables
SELECT
      count(*)                                                   as count_all
    , count(distinct P.ID)                                       as p_id_distinct
    , count(distinct C.ID)                                       as c_id_distinct
    , count(case when C.ID IS NULL then P.ID end)                as c_id_null
    , count(case when S.FallCycle = 1   then P.ID end)           as FallCycle_1
    , count(case when S.FallCycle = 0   then P.ID end)           as FallCycle_0
    , count(case when S.FallCycle not in (1, 0 )  then P.ID end) as FallCycle_not
    , count(case when S.StateFS IS NULL then P.ID end)           as StateFS_null
    , count(case when S.StateFS IS NOT NULL then P.ID end)       as StateFS_not_null
FROM tblProductsNew AS P
LEFT JOIN tblCustomersNew AS C ON P.CustomerNumber = C.CustomerNumber AND P.OfficeNumber = C.OfficeNumber 
LEFT JOIN tblStatesAll    AS S ON C.ResStateCode = S.StateFS
;

Open in new window

the results of this might help identify why you can't reconcile so far.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39636471
just an observation:
FROM tblCustomersNew AS C  --<< C is "prior to S" and "prior to P"

LEFT JOIN tblStatesAll AS S ON S.StateFS = C.ResStateCode   -- "prior table" used in join last

INNER JOIN tblProductsNew AS P
     ON C.CustomerNumber = P.CustomerNumber -- "prior table" used in join first
  AND C.OfficeNumber = P.OfficeNumber

It is my opinion that a "prior table" should always be used first in joins
however, more importantly I believe the approach should be consistently applied no matter which you list first.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now