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.