python 2.7 - SQL Query not working as it should -

so have 3 tables:

authors: -------- id    name 1     john 2     sue 3     mike  authors_publications: --------------------- authorid    paperid 1            1 1            2 2            2 3            1 3            2 3            3  publications: ------------- id         year 1          2004 2          2005 3          2004 

i'm trying join them count number of publications each author has had on 2004. if didn't publish should zero

ideally result should this:

id    name    publications_2004  1    john           1  2    sue            0  3    mike           2 

i tried following:

select a.id, name, count(*) publications_2004  authors_publications ap left join authors on ap.authorid=a.id left join publications p on p.id=ap.paperid  year=2004  group ap.authorid 

i don't understand why it's not working. removing authors haven't published in 2004.

your statement taking result set returned join's , them trimming off records year<>2004.

to around can few different things

you can apply filter publications table in on statement when joining. filter results before joining

 select a.id,     name,     count(*) publications_2004 authors_publications ap left join authors     on ap.authorid = a.id left join publications p     on p.id = ap.paperid ,         p.year = 2004 group ap.authorid 

you use case statement instead of where:

select a.id,     name,     sum(case when p.year = 2004 1 else 0) end publications_2004 authors_publications ap left join authors     on ap.authorid = a.id left join publications p     on p.id = ap.paperid  group ap.authorid, name 

you use subquery pre-filter publications table 2004 records, explicitly doing implicit in first option:

select a.id,     name,     count(*) publications_2004 authors_publications ap left join authors     on ap.authorid = a.id left join (select * publications , year = 2004) p     on p.id = ap.paperid  group ap.authorid, name 

also, because not aggregating name formula, should add group by otherwise may funky results.