0

So if below are the records from four tables

1st table 2nd table 3rd table 4th table
1 1 2. 3
2 2. 6. 4
3. 5. 7. 10

Then A table has 1,2,3 from 1st table,5 from 2nd table,6,7 from third table and 4 and 10 from 4th table.

with a as (
select distinct a.id
from  a inner join 
first table b
on a.id=b.id),
b as(
select distinct a.id
from a inner join 
second table b 
on a.id=b.id
except
select * from a),

d as (
  select * from a
  union
  select * from b
),
c as(
select distinct a.id
from a inner join 
third table b 
on a.id=b.id
except
select * from d)

select count(*) as count from a
union
select count(*) as count from b
union 
select count(*)  as count from c

Here I get correct count from a and b But from c ,if I execute the first join query alone the count is 78471355 and the count() from d alone I get 32579307 as count .When I subtract manually the answer I get is 45892048. But for the query "select count() as count from c" ,I get the output as 48762978.

Please note I am executing this in presto db and also I have a 4th table too to find the count.I didn't add it since the output from c is wrong.Please help.

MT0
  • 143,790
  • 11
  • 59
  • 117
FFchin
  • 1
  • 2

1 Answers1

0

You appear to want to count the id values in table a that also appear in four different tables but comparing the related tables in a priority order; for that, you can use a CASE expression to test whether the id exists in each related table.

SELECT table_name,
       COUNT(*) AS num_rows
FROM   (
  SELECT id,
         CASE
         WHEN EXISTS (SELECT 1 FROM first_table t WHERE a.id = t.id)
         THEN 'first_table'
         WHEN EXISTS (SELECT 1 FROM second_table t WHERE a.id = t.id)
         THEN 'second_table'
         WHEN EXISTS (SELECT 1 FROM third_table t WHERE a.id = t.id)
         THEN 'third_table'
         WHEN EXISTS (SELECT 1 FROM fourth_table t WHERE a.id = t.id)
         THEN 'fourth_table'
         END AS table_name
  FROM   a
)
WHERE  table_name IS NOT NULL
GROUP BY table_name;

Which, for the sample data:

CREATE TABLE a (id) AS
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;

CREATE TABLE first_table (id) AS
  SELECT 1 FROM DUAL UNION ALL
  SELECT 2 FROM DUAL UNION ALL
  SELECT 3 FROM DUAL;

CREATE TABLE second_table (id) AS
  SELECT 1 FROM DUAL UNION ALL
  SELECT 2 FROM DUAL UNION ALL
  SELECT 5 FROM DUAL;

CREATE TABLE third_table (id) AS
  SELECT 2 FROM DUAL UNION ALL
  SELECT 6 FROM DUAL UNION ALL
  SELECT 7 FROM DUAL;

CREATE TABLE fourth_table (id) AS
  SELECT 3 FROM DUAL UNION ALL
  SELECT 4 FROM DUAL UNION ALL
  SELECT 10 FROM DUAL;

Outputs:

TABLE_NAME NUM_ROWS
first_table 3
fourth_table 2
second_table 1
third_table 2

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117