left join multiplying values

I have the following queries -

SELECT COUNT(capture_id) as count_captures 
  FROM captures 
 WHERE user_id = 9

...returns 5

SELECT COUNT(id) as c开发者_运维知识库ount_items 
  FROM items 
 WHERE creator_user_id = 9

...returns 22

I tried the following query -

   SELECT COUNT(capture_id) as count_captures, 
          COUNT(items.id) as count_items 
     FROM captures 
LEFT JOIN items ON captures.user_id = items.creator_user_id 
    WHERE user_id = 9

...but it returns two columns both with 110 as the value. I would want 5 in one column and 22 in the other. What am I doing wrong?

---

My knee-jerk is a subquery:

select count(capture_id) as count_captures, 
    (select count(id) as count_items
         from items i where i.creator_user_id = captures.user_id) as count_items 
from captures 
where user_id = 9

I'm not really sure what you can do to avoid this. You're seeing expected (and generally desired behavior).

Of course, if you know that the ID's in both won't repeat themselves, you can use distinct:

SELECT COUNT( DISTINCT capture_id) as count_captures, 
      COUNT( DISTINCT items.id) as count_items 
FROM captures 
LEFT JOIN items ON captures.user_id = items.creator_user_id 
    WHERE user_id = 9

---

A LEFT JOIN returns each row in the left table with each row in the right table that matches the results. Since all of your id's are the same which produces a Cartesian Product of the table. (5 * 22 = 110).

This is expected to happen.

---

You could always union the results (warning, untested):

SELECT SUM(sub.count_captures), SUM(sub.count_items)
FROM (SELECT COUNT(capture_id) as count_captures, 0 as count_items 
from captures where user_id = 9
UNION
SELECT 0 as count_captures, count(id) as count_items
from items where creator_user = 9) sub

---

Another way to combine two (seemingly not related) queries into one:

SELECT
    ( SELECT COUNT(capture_id) 
        FROM captures 
       WHERE user_id = 9
    )
    AS count_captures 

  , ( SELECT COUNT(id) 
        FROM items 
       WHERE creator_user_id = 9
    )
    AS count_items 

There really is no need for subqueries or JOIN in these cases. Although the optimizer may be smart enough to figure that out, I wouldn't try to confuse him.