CODEWITHSUNDEEP
phpmysqlleft-join
Bruno
Bruno

Reputation: 521

MYSQL joining three tables

I have a simple multiple school management system and I am trying to get total number of teachers, and total number of students for a specific school. My table structures are as follows:

        teachers
--------------------------
id | schoolid | Name | etc...
--------------------------
1  |    1     | Bob  |
2  |    1     | Sarah|
3  |    2     | John |


        students
--------------------------
id | schoolid | Name | etc... 
--------------------------
1  |    1     | Jack |
2  |    1     | David|
3  |    2     | Adam |

        schools
--------------------------
id |      Name       | etc...
---------------------------
 1 | River Park High |
 2 | Stirling High   |

I can count just all teachers with the following query:

SELECT COUNT(a.id) AS `totalteachers` 
FROM teachers a 
LEFT JOIN schools b ON a.schoolid = b.id WHERE b.id = '1'

and similarly I can count the number of teachers with the following query:

SELECT COUNT(a.id) AS `totalstudents` 
FROM students a 
LEFT JOIN schools b ON a.schoolid = b.id WHERE b.id = '1'

I am however struggling with trying to combine these two queries to get a simple result like this:

totalstudents | totalteachers
--------------------------------
       2      |      2

I have tried the following:

SELECT COUNT(a.id) as `totalteachers`, COUNT(c.id) as `totalstudents` 
FROM teachers a 
LEFT JOIN schools b ON a.schoolid = b.id 
LEFT JOIN students c ON c.schoolid=b.id WHERE b.id = '5'

Upvotes: 1

Views: 67

Answers (3)

Andrew Bone
Andrew Bone

Reputation: 7291

You can do something like this

SELECT 
  id, name, s.total AS totalstudents, t.total AS totalteachers
FROM schools
  JOIN (SELECT schoolid, COUNT(id) AS total FROM teachers GROUP BY schoolid)
    AS t ON t.schoolid = id        
  JOIN (SELECT schoolid, COUNT(id) AS total FROM students GROUP BY schoolid)
    AS s ON s.schoolid = id

then you can add where id = 2 or whatever to limit the school.

Upvotes: 3

xQbert
xQbert

Reputation: 35323

The problem with the multiple left joins is it generates additional records for each teacher to each student; artifically inflating your counts

There's four ways to solve this: (best imo is what Andrew bone did)

Simply select inline without the joins so the counts are not inflated. (most desirable in my mind as it's easy to maintain)

SELECT (SELECT COUNT(a.id) AS `totalteachers` 
        FROM teachers a 
        WHERE A.SchoolID = '1') as TotalTeachers
     , (SELECT COUNT(a.id) AS `totalstudents` 
        FROM students a 
        WHERE a.SchoolID = '1') as TotalStudents

Use subqueries to get the counts first before the joins, then join. Since count will always be 1 a cross join works.

SELECT totalTeachers, totalStudents 
FROM (SELECT COUNT(a.id) AS `totalteachers` 
      FROM teachers a 
      LEFT JOIN schools b 
        ON a.schoolid = b.id 
      WHERE b.id = '1')
CROSS JOIN (SELECT COUNT(a.id) AS `totalstudents` 
            FROM students a 
            LEFT JOIN schools b ON a.schoolid = b.id 
            WHERE b.id = '1')

Use key word distinct within the count so as not to replicate the counts and negate the artificial inflation (least desirable in my mind as this hides the artifical count increase)

SELECT COUNT(distinct a.id) as `totalteachers`, COUNT(distinct c.id) as `totalstudents` 
FROM teachers a 
LEFT JOIN schools b ON a.schoolid = b.id 
LEFT JOIN students c ON c.schoolid=b.id WHERE b.id = '5'

Another way would be to use a window functions, however these are not available in mySQL.

Upvotes: 2

florian-lenz
florian-lenz

Reputation: 1

SELECT COUNT(t.id) AS TotalTeachers, COUNT(st.id) AS TotalStudents
FROM schools s
INNER JOIN teachers t
ON s.id = t.schoolid
INNER JOIN students st
ON s.id = st.schoolid

Try this SQL. I havn't try it but it should work.

Upvotes: 0

Related Questions