CODEWITHSUNDEEP
mysqlsqlcountinner-join
tthlaszlo
tthlaszlo

Reputation: 485

MySQL two table inner join and count

I have two tables:

// bookmark
| id | user | title |

// bookmarkItem
| itemID | bookmarkId |

I try to select from one user's all bookmark and count how many items in there. I have a half working sql:

SELECT
    bookmark.id,
    bookmark.title,
    COUNT(bookmarkItem.itemId) AS count
FROM
    bookmark
    INNER JOIN bookmarkItem ON bookmark.id = bookmarkItem.bookmarkId
WHERE
    bookmark.user = 'username'
GROUP BY
    bookmark.id

But it doesn't write the bookmark with zero bookmarkItem-s. What I want is to show that lines to, like:

| id | title | count |
|----|-------|-------|
| 12 | asdfg |   15  |
| 13 | asdfh |   0   |
| 14 | asdfj |  145  |

Upvotes: 0

Views: 79

Answers (2)

Hacker
Hacker

Reputation: 7906

Use LEFT JOIN on bookmark table. This will give all records from bookmark table, even if bookmarkItem does not have any matching record.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

This is because you only select bookmarks with matching bookmark items. (This is how an INNER JOIN works. You need an OUTER JOIN instead.)

Change

INNER JOIN

to

LEFT JOIN

so as to also get bookmarks without bookmark items.

Upvotes: 2

Related Questions