Solved

isolate records that differ based on state

Posted on 2014-01-14
18
216 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
  • 6
  • 4
  • 3
  • +2
18 Comments
 
LVL 25

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:Kdo
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
 

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:Kdo
Kdo 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:Kdo
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

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 25

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 25

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 31

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 31

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 31

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

13 Experts available now in Live!

Get 1:1 Help Now