I have this tables:
L table // teachers
KL | org |
---|---|
1 | test |
2 | test1 |
G table // groups
KG | fac |
---|---|
1 | maths |
2 | physics |
R table
KR | KL | KG |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 2 | 2 |
And I want to make this query: How many orgs have teachers who give lectures at more than one faculty?
So here is my query, but it is not working as intended:
SELECT COUNT(L.org)
FROM (L INNER JOIN R ON L.KL = R.KL)
INNER JOIN G ON G.KG = R.KG
GROUP BY G.fac, L.org
HAVING COUNT(G.fac) > 1;
Expected output: 1 (beacause only teacher with Id 1 have more than one faculty, so count of orgs is 1)
Can you help me to figure out what is wrong?
2
Answers
Retrieve the IDs of all teachers who have lectured to more than one faculty using
GROUP BY
ANDHAVING
clauses, then useCOUNT()
function to count the records in this dataset :Your query is close, but the issue lies in the
GROUP BY
clause. You’re grouping by bothG.fac
andL.org
, which is causing the count to be aggregated for each combination of organization and faculty. Instead, you should only group byL.org
to count the number of organizations with teachers lecturing at more than one faculty.L
,R
, andG
tables to get the necessary information about teachers, their lectures, and the faculties.G.fac
as well, we’re only grouping byL.org
.HAVING
clause checks if the count of distinct faculties associated with each organization (COUNT(DISTINCT G.fac)
) is greater than 1, indicating that the teacher associated with that organization gives lectures at more than one faculty.This query should give you the expected output of 1, which represents the number of organizations with teachers lecturing at more than one faculty.