Solved

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

Posted on 2013-11-08
6
523 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

751 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