Compare two tables on one or two columns with rull reporting

Question

i have two tables that both have portfolios and bins. I want to find rows from each table (1) where they both match (2) where neither portfolio or bin matches (3) where portfolio matches but bin does not. For #2 and #3 I want to know which table has the no match row. I have tried coding it but have a mess of a query. Hoping that someone can help who has a clearer head and more knowledge. Thanks

IF OBJECT_ID('test_all_accounts', 'U') IS NOT NULL  drop table test_all_accounts;
create table test_all_accounts (portfolio varchar(128), bin varchar(128)) ;
insert into test_all_accounts
    select portfolio, bin
      from (values ('p111', 'b111a'), ('p111', 'b111b'),('p222', 'b222a'), ('p222', 'b222a'),('p333', 'b333a')) as v1 (portfolio, bin);

IF OBJECT_ID('test_client_subset', 'U') IS NOT NULL  drop table test_client_subset;
create table test_client_subset (portfolio varchar(128), bin varchar(128)) ;
insert into test_client_subset
    select portfolio, bin
      from (values ('p111', 'b111a'), ('p111', 'b111x'), ('p222', 'b222a'), ('p222', 'b222x'), ('p444', 'b444a')) as v1 (portfolio, bin);

Desired Output

Text,                   portfolio, bin     
P&B Match,              p111, b111a
Accounts Port Match,    p111, b111b
Client Port Match,      p111, b111x
P&B match,              p222, b222a
Accounts Port Match,    p222, b222a
Client Port Match,      p222, b222x
Client P&B Not Match    p444, b444a 
Accounts P&B Not Match  p333, b333a

Answer 1

This query may work for you, although the result does not match our expected exactly. On the other hand, I am not sure your output is consistent.

SELECT 'P&B Match' AS text, a.portfolio, a.bin
FROM   test_all_accounts a
WHERE  EXISTS (SELECT * 
               FROM   test_client_subset c
               WHERE  a.portfolio = c.portfolio
                 AND  a.bin       = c.bin)
UNION ALL
SELECT 'Accounts port match' AS text, a.portfolio, a.bin
FROM   test_all_accounts a
WHERE  EXISTS (SELECT * 
               FROM   test_client_subset c
               WHERE  a.portfolio = c.portfolio)
   AND NOT EXISTS (SELECT * 
                   FROM   test_client_subset c
                   WHERE  a.portfolio = c.portfolio
                     AND  a.bin       = c.bin)
UNION ALL
SELECT 'Client port match' AS text, c.portfolio, c.bin
FROM   test_client_subset c
WHERE  EXISTS (SELECT * 
               FROM   test_all_accounts a
               WHERE  a.portfolio = c.portfolio)
  AND  NOT EXISTS (SELECT * 
                   FROM   test_all_accounts a
                   WHERE  a.portfolio = c.portfolio
                     AND  a.bin       = c.bin)
UNION ALL
SELECT 'Accounts P&B NOT match' AS text, a.portfolio, a.bin
FROM   test_all_accounts a
WHERE  NOT EXISTS (SELECT * 
                   FROM   test_client_subset c
                   WHERE  a.portfolio = c.portfolio)
UNION ALL
SELECT 'Client P&B not match' AS text, c.portfolio, c.bin
FROM   test_client_subset c
WHERE  NOT EXISTS (SELECT * 
                   FROM   test_all_accounts a
                   WHERE  a.portfolio = c.portfolio)
ORDER BY portfolio, bin

Answer 2

Hi @brenda grossnickle ,

Please also refer below:

select 'P&B Match' AS text,a.*  
from test_all_accounts a  
inner join test_client_subset b   
on a.portfolio=b.portfolio and a.bin=b.bin  
Union all  
select 'Accounts P&B Not Match' AS text,a.*  
from test_all_accounts a  
left join test_client_subset b   
on a.portfolio=b.portfolio   
where b.portfolio is null   
Union all  
select 'Client P&B Not Match' AS text,b.*  
from test_all_accounts a  
right join test_client_subset b   
on a.portfolio=b.portfolio   
where  a.portfolio is null   
Union all  
select 'Accounts Port Match' AS text,* from   
(select a.*  
from test_all_accounts a  
inner join test_client_subset b   
on a.portfolio=b.portfolio   
except   
select a.*  
from test_all_accounts a  
inner join test_client_subset b   
on a.portfolio=b.portfolio and a.bin=b.bin)c  
Union all  
select 'Client Port Match' AS text,* from   
(select b.*  
from test_all_accounts a  
inner join test_client_subset b   
on a.portfolio=b.portfolio   
except   
select b.*  
from test_all_accounts a  
inner join test_client_subset b   
on a.portfolio=b.portfolio and a.bin=b.bin)c  
ORDER BY portfolio, bin  

Output:

text	portfolio	bin  
P&B Match	p111	b111a  
Accounts Port Match	p111	b111b  
Client Port Match	p111	b111x  
P&B Match	p222	b222a  
P&B Match	p222	b222a  
Client Port Match	p222	b222x  
Accounts P&B Not Match	p333	b333a  
Client P&B Not Match	p444	b444a  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.