select `personal`.`id` AS `id`,
`personal`.`name` AS `name`,
(select count(visit.id)
from visit,personal
where visit.user_id=personal.id) as count
from personal;
im trying to get all users and the counts of visits they did.
the result i get is all users but the count column contain same value (not specific to that row id).
what am i doing wrong here ? how to tell mysql to user this row id ?
is compound select optimum way to do it or is there a better way ?
SELECT p.id, p.name, COUNT(v.user_id)
FROM personal p
LEFT JOIN
visit v
ON v.user_id = p.id
GROUP BY
p.id
You may also use subselect of course (for instance if you have ANSI
GROUP BY
compatibility on):
SELECT p.id, p.name,
(
SELECT COUNT(*)
FROM visit v
WHERE v.user_id = p.id
)
FROM personal p
try this
SELECT
Pe.id AS id,Pe.name AS name,COUNT(v.user_id) number_visit
FROM personal Pe
LEFT JOIN visit Vi
ON Vi.user_id= Pe.id
GROUP BY Pe.id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With