COUNT() twice in one query showing weird results
Ok this is my query:
It's giving me 10 as dragon_count and 10 as pm_count when in reality, it should be 1 as dragon_count and 10 for pm_count.
SELECT COUNT(d.dragon_id) as dragon_count, COUNT(pm.id) as pm_count
FROM user_creature d
LEFT JOIN private_message pm ON pm.to_user = d.user_id
WHERE d.user_id = 4
Why does dragon_count go up to 10 when I add on the private_message table and do the COUNT() for that. Without the private_message table included and in the SELECT, it pulls 1 just fine..