I am having trouble understanding why my two SQL queries output different results for the count of senior managers when I expected them to be the same.
SELECT COMPANY.Company_Code, COMPANY.Founder, COUNT(SENIOR_MANAGER.Senior_Manager_Code)
FROM COMPANY INNER JOIN SENIOR_MANAGER
ON SENIOR_MANAGER.COMPANY_CODE = COMPANY.COMPANY_CODE
GROUP BY COMPANY.COMPANY_CODE, COMPANY.FOUNDER;
This SQL query is trying to find out how many senior managers there are in different companies, here are the results:
C1 Angela 5
C10 Earl 2
C100 Aaron 4
C11 Robert 1
C12 Amy 6
However, when I use the same condition in another query where I use two inner joins I get another set of results. Here is my query:
SELECT COMPANY.COMPANY_CODE, COMPANY.FOUNDER, COUNT(LEAD_MANAGER.LEAD_MANAGER_CODE),
COUNT(SENIOR_MANAGER.SENIOR_MANAGER_CODE)
FROM COMPANY INNER JOIN LEAD_MANAGER
ON COMPANY.COMPANY_CODE = LEAD_MANAGER.COMPANY_CODE
INNER JOIN SENIOR_MANAGER
ON SENIOR_MANAGER.COMPANY_CODE = COMPANY.COMPANY_CODE
GROUP BY COMPANY.COMPANY_CODE, COMPANY.FOUNDER;
Here are the results:
C1 Angela 10 10
C10 Earl 2 2
C100 Aaron 8 8
C11 Robert 1 1
C12 Amy 12 12
The fourth column is the count of the senior managers & has the same values as the third column for some reason but has different values from the first query I showed. Can someone explain why the results are different I suspect it could be because I am using the inner joins incorrectly?
The desired result I expect is for the fourth column to have the values from the first query shown.
