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.

Thanks in advanced.

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

Answer 2

You want a left join, instead of an inner join, as that allows records to not exist.

Answer 3

LEFT join is your friend. To learn more about different join types refer to http://en.wikipedia.org/wiki/Join_(SQL)

Answer 4

Accepted answer does not work on MSSQL, cannot use page.name in the select clause when it does not appear in the group clause,

Here's another solution using sub-querying.

SELECT
  p.name,
  (
    SELECT COUNT(*) FROM [page-attachment] pa
    WHERE pa.[PAGE-ID] = p.id
  ) as attachmentsnumber
FROM page p