Join other table to get count

Question

I have a table contains items:

items: id, body, user_id

and second table constains votes:

items_votes: id, item_id, type

I getting all items by simple query:

SELECT * FROM items

How I can get votes count for every item in this query?

Answer 1

try out this...

select item.id, item.type, item.user_id, count(*)
FROM items item inner join
items_votes iv on item.id = iv.item_id
group by item.id

SQL Fiddle

Answer 2

select i.id, i.type, i.user_id, count(*)
FROM items i,items_votes v WHERE i.id = v.item_id
group by i.id

SQLFIDDLE