I am struggling with my statement in PostgreSQL. I have 3 tables:
tbl - flags (labeled as a)
a.email
tbl - accounts (labeled as b)
b.id
b.email
tbl - foo (labeled as c)
c.account_id
c.total
My sql statement looks like:
select a.email, sum(c.total)
from flags a, accounts b, foo c
where a.email = b.email and b.id = c.account_id
This statement works but there could be some accounts who does not have an account or total amount either. Well, this statement does not make sense, because there are several a.emails
in flags a
, which don’t have any c.total
record. And also there are several b.emails
in accounts b
, who doesn’t have any record in c.total
.
My desired output:
email – sum(i.total)
foo@foo.com 5666
kjkj@kjaksd.com 882
hello@world.com 23122
mac@toast.com 0 -- mac@toast.com doesn’t have account or any total
So is it possible to check if a record exist in accounts compare to flags? And if so, let’s prompt total amount and if not, let’s prompt 0
.
Best Answer
First, the query you have is not valid. I assume you omitted the
GROUP BY a.email
when posting the question. You also aliasfoo
asc
but then you reference toi
.total
.Now for the missing results, it's because you are using (implicit) inner joins. That they are implicit, it doesn't affect results. But when using explicit joins, it's easy to change them from
INNER
toLEFT
join.So by doing
a LEFT JOIN b
we keep results froma
that have no matching row inb
and by doingb LEFT JOIN c
we keep results fromb
that have no matching row inc
.The query finally looks like this (adding
COALESCE()
so we get zeros and not nulls for the emails that have no account or total):