SQL Query, Count with 0 count

Question

I have three tables: page, attachment, page-attachment

I have data like this:

page
ID    NAME
1     first page
2     second page
3     third page
4     fourth page

attachment
ID    NAME
1     foo.word
2     test.xsl
3     mm.ppt

page-attachment
ID    PAGE-ID   ATTACHMENT-ID
1     2         1
2     2         2
3     3         3

I would like to get the number of attachments per page also when that number is 0. I have tried with:

select page.name, count(page-attachment.id) as attachmentsnumber 
from page 
    inner join page-attachment on page.id=page-id 
group by page.id

I am getting this output:

NAME        ATTACHMENTSNUMBER
second page  2
third page   1

I would like to get this output:

NAME        ATTACHMENTSNUMBER
first page   0
second page  2
third page   1
fourth page  0

How do I get the 0 part?

Answer 1

Change your "inner join" to a "left outer join", which means "get me all the rows on the left of the join, even if there isn't a matching row on the right."

select page.name, count(page-attachment.id) as attachmentsnumber 
from page 
    left outer join page-attachment on page.id=page-id 
group by page.name