isolate records that differ based on state

I have two tables. A and B.

I ran two seperate queries to give me all records of customers in state of CA for table A and table B.
table A returned 73 records in CA
table B returned 75 records in CA

I am trying to write a sql command to tell me which are the two records in table B that are not in table A. table A and table B can be joined using customer ID.
paul_techyAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Is it possible that we are incorrectly assuming that 75 - 73 = 2 records in table B that are not in table A? Could the possibility exist that you have records in table a that are not in table b and that table b has records that do not exist in table a?
0
 
Shaun KlineLead Software EngineerCommented:
Try using the EXCEPT clause.
It works as such:

select customerID
from tableB
where state = 'CA'
EXCEPT
select customerid
from tableA
where state = 'CA'

Open in new window


This assumes that you do not have multiple rows with the same customer ID.
0
 
Simone BSenior E-Commerce AnalystCommented:
You could use a subquery:

SELECT * FROM TableB 
WHERE [State] = 'CA' 
AND CustomerID NOT IN 
	(SELECT CustomerID FROM TableB
	WHERE [State] = 'CA')

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kent OlsenData Warehouse Architect / DBACommented:
You could actually have records from A that aren't in B and records in B that aren't in A.  You just have two more in B.

From you basic example, an outer join works fine, so does the IN predicate.

SELECT b.id
FROM b 
LEFT JOIN a
  ON b.id = a.id
WHERE a.id is NULL

Open in new window


You should also flip that around to find the rows in A that aren't in B, or use a FULL JOIN to find them.

SELECT b.id, a.id
FROM b 
FULL JOIN a
  ON b.id = a.id
WHERE a.id is NULL
   OR b.id is NULL

Open in new window



Good Luck,
Kent
0
 
paul_techyAuthor Commented:
Shaun Kline:  i didnt try yours because there does exist multiple rows with the same customer ID.

Simone B: I tried your code and got 5 records.  2 ID's are the same. Also I assume the second tableB reference is actually tableA

KDO: i tried your first code snippet only and got 36 records. Non are in state CA.
0
 
Simone BConnect With a Mentor Senior E-Commerce AnalystCommented:
Sorry 'bout that. Yes, it should be TableA

SELECT * FROM TableB 
WHERE [State] = 'CA' 
AND CustomerID NOT IN 
	(SELECT CustomerID FROM TableA
	WHERE [State] = 'CA')

Open in new window


If some of your results are the same, that sounds like, as you told Shaun Kline, you have duplicate IDs. What happens when you use distinct? And are you certain there can only be 2 missing?

SELECT DISTINCT CustomerID FROM TableB 
WHERE [State] = 'CA' 
AND CustomerID NOT IN 
	(SELECT CustomerID FROM TableA
	WHERE [State] = 'CA')

Open in new window

0
 
paul_techyAuthor Commented:
Simone B:

Results now show 4 becuase it removed the one duplicate.

On my other query I dont use distinct and I am using Crystal Reports and it shows total records at the botton the program. Records: 73 (A), Records: 75 (B)

table A - query
select * from tableA where company='X' and state='CA'

table B - query
select * from tableB where company='X' and state='CA'
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Simone,

KDO: i tried your first code snippet only and got 36 records. Non are in state CA.

With only a skeleton of your table description, I only posted a shell of the possible query.  Of course you have to filter by 'CA'

SELECT b.id
FROM b
LEFT JOIN a
  ON b.id = a.id
 AND a.state = b.state
 AND a.state = 'CA'
WHERE a.id is NULL
0
 
paul_techyAuthor Commented:
KDO: your query resulted in 10,000 records and none were in CA
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Weird

SELECT b.id
FROM b
LEFT JOIN a
  ON b.id = a.id
 AND a.state = b.state
WHERE a.id is NULL
 AND a.state = 'CA'
0
 
awking00Commented:
The left join will give you a row for every record in b where state = 'CA'. What happens when you also filter on company = 'X"?
0
 
Shaun KlineLead Software EngineerCommented:
If you want to find all rows in b with a state of CA that do not match on customer ID against A with a state of CA, use this variant of Kdo's query above:
SELECT B.*
FROM B
    LEFT JOIN A on B.customerID = A.customerID
         AND A.state = 'CA'
WHERE B.state = 'CA'
    AND A.customerID IS NULL

Open in new window


If you also need to include company, add that to the LEFT JOIN clause.
0
 
paul_techyAuthor Commented:
Shaun Kline: "If you want to find all rows in b with a state of CA that do not match on customer ID against A with a state of CA, use this variant of Kdo's query above"

Tried this and gave me 5 ID numbers. 2 are the same ID numbers

Kdo:
I tried you last query below and gave me zero records.

SELECT b.id
FROM b
LEFT JOIN a
  ON b.id = a.id
 AND a.state = b.state
WHERE a.id is NULL
 AND a.state = 'CA' 

Open in new window

0
 
awking00Commented:
with cte as
(select b.id from tableb where state = 'CA' and company = 'X'
 intersect
 select a.id from tablea where state = 'CA' and company = 'X')
select id from tableb
except
select id from cte;
0
 
paul_techyAuthor Commented:
Shaun Kline: yes that is likely the case. I setup a small test database to prove your theory.

awking00: in the code below is the line in bold correct what table id is that for

with cte as
(select b.id from tableb where state = 'CA' and company = 'X'
 intersect
 select a.id from tablea where state = 'CA' and company = 'X')
select id from tableb
except
[b]select id from cte;[/b] 

Open in new window

0
 
awking00Connect With a Mentor Commented:
cte (common table expression) represents a table that contains all of the ids from both tables where the state = 'CA' and the company = 'X'. For example, if tablea contained ids of 1,2,3,4 and tableb contained ids of 2,3,4,5, then cte would contain ids of 2,3,4 and selecting id from tableb would only contain 5 (2,3,4,5 except [minus] 2,3,4). In your example, where the tablea query returned 73 ids and the tableb query returned 75 ids, the intersection might only return 70 ids, meaning tableb contained 5 ids that were not in tablea (and further means tablea contained 3 ids that were not in tableb).
0
 
awking00Commented:
This -
>> and selecting id from tableb would only contain 5 ... <<
should have read -
>> and selecting id from tableb except select id from cte would only contain 5 ... <<
0
 
paul_techyAuthor Commented:
Thanks for your help with this. I know have a better understanding of how to run this type of query and what I was overlooking.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.