sql - Get count of items and their values in one column

Question

Consider these Tables:

Table Items:

ItemID    ItemName
------------------
 1          N1
 2          N2 
 3          N4
 4          N5

and in MyTbl table I have a ItemID that may be like this:

ItemId
----
1
1
3
4
4
4

I want to write a query that return this result:

ItemId        count
-------------------
   1            2
   2            0
   3            1
   4            3

How I can do this without Cursors?

Answer 1

You can select every item from Items and LEFT JOIN MyTbl on the common ItemID, counting the matches;

select 
   Items.itemId,
   count(MyTbl.itemId) as count
from Items
   left join MyTbl on (MyTbl.ItemID = Items.ItemID)
group by Items.itemId
order by Items.itemId