CODEWITHSUNDEEP
mysqlsqlleft-join
Meng Gao
Meng Gao

Reputation: 3

use 2 left join can't work but separately can get results

I have three tables, company, user and share. I want to count one company's user and share, they are not relevant.

There may be a row that has share value but not user value. so I used left join, I can get results separately, but it doesn't work together.

Here is my query:

SELECT c.name, count(u.company_id), count(s.company_id)
FROM company c
LEFT JOIN user u
ON c.id=u.company_id and u.company_id=337
WHERE u.company_id is NOT NULL 
LEFT JOIN share s
ON c.id=s.id AND s.company_id=337 
WHERE s.company_id is NOT NULL

Upvotes: 0

Views: 129

Answers (2)

Barmar
Barmar

Reputation: 780879

You need to do at least one of the counts in a subquery. Otherwise, both counts will be the same, since you're just counting the rows in the resulting cross product.

SELECT c.name, user_count, share_count
FROM company AS c
JOIN (SELECT company_id, COUNT(*) AS user_count
      FROM users
      GROUP BY company_id) AS u
ON u.company_id = c.id
JOIN (SELECT company_id, COUNT(*) AS share_count
      FROM share 
      GROUP BY company_id) AS s
ON s.company_id = c.id
WHERE c.company_id = 337

Another option is to count the distinct primary keys of the tables you're joining with:

SELECT c.name, COUNT(DISTINCT u.id) AS user_count, COUNT(DISTINCT s.id) AS share_count
FROM company AS c
JOIN users AS u on u.company_id = c.id
JOIN share AS s ON s.company_id = c.id
WHERE c.company_id = 337

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Your code looks okay, except for the extra WHERE clause. However, you probably want COUNT(DISTINCT), because the two counts will return the same value:

SELECT c.name, count(distinct u.company_id), count(distinct s.company_id)
FROM company c LEFT JOIN
     user u
     ON c.id = u.company_id and u.company_id=337 LEFT JOIN
     share s
     ON c.id = s.id AND s.company_id=337 
WHERE s.company_id is NOT NULL AND u.company_id IS NOT NULL;

Upvotes: 0

Related Questions