Solved

isolate records that differ based on state

Posted on 2014-01-14
18
224 Views
Last Modified: 2014-02-07
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.
0
Comment
Question by:paul_techy
[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
  • 4
  • 3
  • +2
18 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39780420
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
 
LVL 11

Expert Comment

by:Simone B
ID: 39780432
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39780435
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:paul_techy
ID: 39780717
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
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 125 total points
ID: 39780734
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
 

Author Comment

by:paul_techy
ID: 39780762
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
 
LVL 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 125 total points
ID: 39780768
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
 

Author Comment

by:paul_techy
ID: 39780941
KDO: your query resulted in 10,000 records and none were in CA
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39781108
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
 
LVL 32

Expert Comment

by:awking00
ID: 39782952
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
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39783359
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
 

Author Comment

by:paul_techy
ID: 39786424
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
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 125 total points
ID: 39786821
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
 
LVL 32

Expert Comment

by:awking00
ID: 39786862
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
 

Author Comment

by:paul_techy
ID: 39787142
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 39788410
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
 
LVL 32

Expert Comment

by:awking00
ID: 39788417
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
 

Author Closing Comment

by:paul_techy
ID: 39842195
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

756 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