mysql - How aright get count with left joing?

Question

Structure tables on sqlfiddle

if i use query:

SELECT 
    ei.ItemIdn as ItemIdn,
    ei.UserIdn as UserIdn

FROM items ei

WHERE ei.UserIdn = '0000' 
LIMIT 0, 25

I get 4 rows, but if i want get count viewers for each row:

SELECT 
    ei.ItemIdn as ItemIdn,
    ei.UserIdn as UserIdn,
    ifnull(count(cv.ItemIdn),0) as CountShow

FROM items ei

    left join CountViewes as cv on ei.ItemIdn = cv.ItemIdn

WHERE ei.UserIdn = '0000' 
LIMIT 0, 25

I get only one row.

Tell me please how get all rows and count(cv.ItemIdn) for each row?

Answer 1

I do believe your query needs to end with GROUP BY because it contains an aggregate function.

GROUP BY ei.ItemIdn, ei.UserIdn

See here: http://sqlfiddle.com/#!2/24d19/32/0

Answer 2

I think you are just missing the GROUP BY clause - http://sqlfiddle.com/#!2/24d19/26

SELECT 
    ei.ItemIdn as ItemIdn,
    ei.UserIdn as UserIdn,
    COUNT(cv.ItemIdn) as CountShow
FROM items ei
LEFT JOIN CountViewes as cv on ei.ItemIdn = cv.ItemIdn
WHERE ei.UserIdn = '0000' 
GROUP BY ItemIdn, UserIdn
LIMIT 0, 25