CODEWITHSUNDEEP
mysqlsql
user1434156
user1434156

Reputation:

Query using count with foreign key involved

In the query I am fetching data from three tables : company, classes_by_company, and person. I have a foreign key in all tables with the name company_id. I use a left join to mesh the tables with a matching company_id. I am trying to find out the amount of classes per company and employees. I am getting values that are not correct for both. Here is a SQIDDLE 

SELECT a.id, 
a.company_id,
a.status,
COUNT(c.company_id) AS classes_per_company,
COUNT(p.employee_id) AS employees_per_company
FROM company a
LEFT JOIN classes_by_company c
ON a.company_id = c.company_id
LEFT JOIN person p
ON a.company_id = p.company_id
GROUP BY a.company_id

Table structure:

CREATE TABLE company
    (
     id int auto_increment primary key,
     company_id int,
     status varchar(20)
    );
CREATE TABLE classes_by_company
(
 id int auto_increment primary key,
 company_id int,
 class_name varchar(20)
);
CREATE TABLE person
(
 id int auto_increment primary key,
employee_id int,
 company_id int,
 person_name varchar(20)
);

enter image description here

Upvotes: 3

Views: 102

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

i think you need to COUNT(DISTINCT c.company_id) since your data has entries of the same company_id and class

SELECT a.id, 
       a.company_id,
       a.status,
       COUNT(DISTINCT c.id) AS classes_per_company,
       COUNT(DISTINCT p.employee_id) AS employees_per_company
FROM company a
LEFT JOIN classes_by_company c ON a.company_id = c.company_id
LEFT JOIN person p ON a.company_id = p.company_id
GROUP BY a.id,a.company_id,a.status

according to your updated question: you only need COUNT(DISTINCT p.employee_id)

also i think you should COUNT(DISTINCT c.id) instead of COUNT(c.company_id) since you're trying to count classes and not companies.

Upvotes: 1

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

I have to ask - why does company have both company_id and id? Shouldn't one of them be sufficient? Regardless of that, your problem is due to the multiplicative nature of joins - that is, if there are 2 employees at a company, and 2 classes, you end up with 4 rows - 1 for each combination (person A + class A, person A + class B, person B + class A, person B + class B). Due to this, here's the other way the issue is usually solved:

SELECT a.id, a.company_id, a.status,
       c.count AS classes_per_company,
       p.count AS employees_per_company
FROM company a
LEFT JOIN (SELECT company_id, COUNT(*) as count
           FROM classes_by_company
           GROUP BY company_id) c
       ON a.company_id = c.company_id
LEFT JOIN (SELECT company_id, COUNT(*) as count
           FROM person
           GROUP BY company_id) p
       ON a.company_id = p.company_id

(and the resulting fiddle - thanks for providing one!)

Note that, while both answers give the correct result, this version is likely to be more performant, as it has a higher chance to be using indices.

Upvotes: 1

Related Questions