MYSQL count related rows in 2 tables

Question

I have 3 tables

products table
productid   productname
---------   -----------
 1          product 1
 2          product 2
 5          product 3
 10         product 4
 11         product 5
 12         product 6

accounts_products table
id  productid   accountid
--  ---------   ---------
1   1           accountid 1
2   10          accountid 2
3   2           accountid3

leads_products table
id  productid    leadid
--  ---------    ---------
1   1            leadid 1
2   5            leadid 2
3   2            leadid 3

I am trying to count how many total products are in leads_products and accounts_products tables based on the same productid's.

Expected result

Product ID    Product Name    Total
-----------   ------------    --------
1             product 1         2
2             product 2         2
5             product 3         1
10            product 4         1

I tried so far

SELECT p.productid as 'Product ID', 
       p.productname as 'Product Name', 
       COUNT(*) as 'Total' FROM products p
INNER JOIN leads_products l ON (l.productid=p.productid)
INNER JOIN accounts_products a ON (a.productid=p.productid)
GROUP BY p.productname,p.productid

Above query counts and display higher number than expected.

I hope it makes sense.

Answer 1

Try this:

SELECT p.productid as 'Product ID', 
       p.productname as 'Product Name', 
       (SELECT COUNT(*)
        FROM leads_products AS l
        WHERE l.productid = p.productid) + 
       (SELECT COUNT(*)
        FROM accounts_products AS a
        WHERE a.productid=p.productid) AS 'Total'
FROM products AS p

http://www.sqlfiddle.com/#!2/f8472/5

Alternative approach using JOIN (better performance):

SELECT p.productid as 'Product ID', 
       p.productname as 'Product Name', 
       IFNULL(l.count, 0) + IFNULL(a.count, 0) as 'Total'
FROM products AS p
LEFT JOIN (
    SELECT productid, COUNT(*) AS count
    FROM leads_products
    GROUP BY productid
) AS l
ON l.productid = p.productid
LEFT JOIN (
    SELECT productid, COUNT(*) AS count
    FROM accounts_products
    GROUP BY productid
) AS a
ON a.productid = p.productid

http://www.sqlfiddle.com/#!2/f8472/33