COUNT() twice in one query showing weird results

[lilmousiee]

Ok this is my query:

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
LIMIT 1

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.
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..

[Old Pedant]

Nope, you are wrong.

Let's demonstrate why:

SELECT d.dragon_id, pm.id AS message_id
FROM user_creature d
LEFT JOIN private_message pm ON pm.to_user = d.user_id
WHERE d.user_id = 4

Don't you get 10 rows from that?

So aren't there 10 rows with a dragon_id? Yes, they are all the same id. But that's irrelevant, to SQL.

So of course when you then do COUNT(dragon_id) you get 10.

*TRIVIAL* fix:

SELECT COUNT(DISTINCT 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
LIMIT 1

Hmm??

[Old Pedant]

By the way, your LIMIT 1 is a waste of code. That query will never return more than one row, as it is written.

[lilmousiee]

Wow.. you're right. I'm so confused.

The LIMIT 1 is a faster query. A lot of developers have notified me that I should be including LIMIT 1 in my queries.. Now I shouldn't?

[Old Pedant]

Try it both with and without the LIMIT 1 and see if your results are any different.

If they are--with that query written as is--I owe you a latte.

You only use LIMIT when your query is going to return more records than you want.

And in any case, a LIMIT used *WITHOUT* and ORDER BY clause is worse than useless.