MYSQL count related rows in 2 tables

mysql

Solution

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

Problem

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.

Original source