question

brendagrossnickle-6913 avatar image
0 Votes"
brendagrossnickle-6913 asked brendagrossnickle-6913 commented

Compare two tables on one or two columns with rull reporting

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
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered brendagrossnickle-6913 commented

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes my Desired Output was inconsistent. Thanks for your help.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @brendagrossnickle-6913,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.