Get matching values from multiple tables in SQL

Question

I have 6 tables with IDs of customers. It is posible, in one query, to get the customers ids that coincide in all tables? In my example, the customers ids that results are 8 and 9 (it must be taken into account, that in practice, any of the tables may have values or not)

Many thanks Best regards

DECLARE @TCustomerAge  TABLE(
CustomerID  INT not null ); 


DECLARE @TCustomerLocalization  TABLE(
CustomerID  INT not null ); 


DECLARE @TCustomerGroup  TABLE(
CustomerID  INT not null ); 


DECLARE @TCustomerStore  TABLE(
CustomerID  INT not null ); 


DECLARE @TCustomerSales  TABLE(
CustomerID  INT not null ); 


DECLARE @TCustomerPayment  TABLE(
CustomerID  INT not null ); 


--@TCustomerAge empty
insert into @TCustomerLocalization values (8), (9), (11)
insert into @TCustomerGroup values (8), (9), (10), (11)
insert into @TCustomerStore values (8), (9), (11)
--@@TCustomerSales empty
insert into @TCustomerPayment values (2), (3), (5), (8), (9), (10)

-- Result -> 8, 9

If the ID had to be in all tables would the result of this query be nothing as you have 2 empty tables? And why would 8,9,11 be the right answer as only 8 and 9 show up in all tables that have data? — JMabee
– JMabee, Commented May 23, 2019 at 14:35
in those tables, I save the ids that meet certain rules, for that reason it is possible that exists empty tables. Sorry for the result mistake, I correct it — Skymett
– Skymett, Commented May 23, 2019 at 14:42
Seems to me this would be a full outer join between all the tables on customerID where customerID is not null in any of the tables. — xQbert
– xQbert, Commented May 23, 2019 at 14:48

Max Szczurek · Accepted Answer · 2019-05-23 17:07:27Z

2

To handle empty tables, perhaps union would be a better approach:

with t as (
      select id, '@TCustomerAge' as tablename from @TCustomerAge 
      union  -- uses union to remove duplicates within tables
      select id, '@TCustomerLocalization' from @TCustomerLocalization
      union
      . . .
     )
select t.id
from t cross join
     (select count(distinct tablename) as n from t) n
group by t.id, n.n
having count(*) = n.n;

Empty tables are not represented in t, so the count(distinct) only counts tables that are present.

edited May 23, 2019 at 17:07

Max Szczurek

4,3342 gold badges23 silver badges33 bronze badges

answered May 23, 2019 at 14:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Max Szczurek · Accepted Answer · 2019-05-23 14:42:30Z

;with CteAllIDs as (
    SELECT CustomerID FROM @TCustomerAge UNION
    SELECT CustomerID FROM @TCustomerLocalization UNION
    SELECT CustomerID FROM @TCustomerGroup UNION
    SELECT CustomerID FROM @TCustomerStore UNION
    SELECT CustomerID FROM @TCustomerSales UNION
    SELECT CustomerID FROM @TCustomerPayment
)
SELECT CteAllIDs.CustomerID
FROM CteAllIDs
WHERE (EXISTS (SELECT * FROM @TCustomerAge WHERE CustomerID = CteAllIDs.CustomerID) 
    OR NOT EXISTS (SELECT * FROM @TCustomerAge))
AND (EXISTS (SELECT * FROM @TCustomerLocalization WHERE CustomerID = CteAllIDs.CustomerID) 
    OR NOT EXISTS (SELECT * FROM @TCustomerLocalization))
AND (EXISTS (SELECT * FROM @TCustomerGroup WHERE CustomerID = CteAllIDs.CustomerID) 
    OR NOT EXISTS (SELECT * FROM @TCustomerGroup))
AND (EXISTS (SELECT * FROM @TCustomerStore WHERE CustomerID = CteAllIDs.CustomerID) 
    OR NOT EXISTS (SELECT * FROM @TCustomerStore))
AND (EXISTS (SELECT * FROM @TCustomerSales WHERE CustomerID = CteAllIDs.CustomerID) 
    OR NOT EXISTS (SELECT * FROM @TCustomerSales))
AND (EXISTS (SELECT * FROM @TCustomerPayment WHERE CustomerID = CteAllIDs.CustomerID) 
    OR NOT EXISTS (SELECT * FROM @TCustomerPayment))

Collectives™ on Stack Overflow

Get matching values from multiple tables in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related