<p>I have three tables: page, attachment, page-attachment</p>

<p>I have data like this:</p>

<pre><code>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
</code></pre>

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

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

<p>I am getting this output: </p>

<pre><code>NAME        ATTACHMENTSNUMBER
second page  2
third page   1
</code></pre>

<p>I would like to get this output:</p>

<pre><code>NAME        ATTACHMENTSNUMBER
first page   0
second page  2
third page   1
fourth page  0
</code></pre>

<p>How do I get the 0 part?</p>

[{'answer_id': 36762, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 6, 'selected': True, 'text': '<p>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."</p>\n\n<pre><code>select page.name, count(page-attachment.id) as attachmentsnumber \nfrom page \n    left outer join page-attachment on page.id=page-id \ngroup by page.name\n</code></pre>\n'}, {'answer_id': 36763, 'author': 'David Wengier', 'author_id': 489, 'author_profile': 'https://Stackoverflow.com/users/489', 'pm_score': 1, 'selected': False, 'text': '<p>You want a left join, instead of an inner join, as that allows records to not exist.</p>\n'}, {'answer_id': 36764, 'author': 'aku', 'author_id': 1196, 'author_profile': 'https://Stackoverflow.com/users/1196', 'pm_score': 1, 'selected': False, 'text': '<p>LEFT join is your friend.\nTo learn more about different join types refer to <a href="http://en.wikipedia.org/wiki/Join_(SQL)" rel="nofollow noreferrer">http://en.wikipedia.org/wiki/Join_(SQL)</a></p>\n'}, {'answer_id': 86385, 'author': 'Amy B', 'author_id': 8155, 'author_profile': 'https://Stackoverflow.com/users/8155', 'pm_score': 3, 'selected': False, 'text': "<p>Here's another solution using sub-querying.</p>\n\n<pre><code>SELECT\n  p.name,\n  (\n    SELECT COUNT(*) FROM [page-attachment] pa\n    WHERE pa.[PAGE-ID] = p.id\n  ) as attachmentsnumber\nFROM page p\n</code></pre>\n"}, {'answer_id': 28178720, 'author': 'David M Goodwin', 'author_id': 4500153, 'author_profile': 'https://Stackoverflow.com/users/4500153', 'pm_score': 2, 'selected': False, 'text': '<p>Depending on the database, for speed, you could use the UNION command.</p>\n\n<p>The SQL is longer, but, depending on the database, it speeds things up by seperating "count things that are there" and "count things that are not there".</p>\n\n<pre><code>(\nselect page.name, count(page-attachment.id) as attachmentsnumber \nfrom page \ninner join page-attachment on page.id=page-id \ngroup by page.id\n)\nUNION\n(\nselect page.name, 0 as attachmentsnumber \nfrom page\nwhere page.id not in (\n    select page-id from page-attachment)\n)   \n</code></pre>\n\n<p>The database I need this solution for has 20 pages in more than a million attachments.  The UNION made it run in 13 seconds rather than so long I got bored and tried another way (somewhere above 60 seconds before I killed the outer join  and subquery methods).</p>\n'}, {'answer_id': 28178806, 'author': 'Jpasker', 'author_id': 4438190, 'author_profile': 'https://Stackoverflow.com/users/4438190', 'pm_score': 0, 'selected': False, 'text': '<p>Use this:</p>\n\n<pre><code>SELECT p.name,(\n    SELECT COUNT(*) FROM [page-attachment] pa WHERE pa.[PAGE-ID] = p.id) as attachmentsnumber\nFROM page p\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/36760', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2138/']