CODEWITHSUNDEEP
mysql
librium
librium

Reputation: 39

MYSQL count related rows in 2 tables

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.

Upvotes: 3

Views: 1950

Answers (2)

Scarecrow
Scarecrow

Reputation: 203

I did it with a left join of p.products to preserve all the products then counted the products in each list and added them together. Worked first try. There's probably a billion ways to do this.

SELECT p.productid 'Product ID', 
       p.productname 'Product Name', 
       COUNT(a.accountid) + COUNT(l.leadid) total
FROM products p
LEFT JOIN accounts_products a ON p.productid = a.productid
LEFT JOIN leads_products l ON p.productid = l.productid
GROUP BY p.productid

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

Upvotes: -1

Mark Byers
Mark Byers

Reputation: 838106

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

Upvotes: 10

Related Questions