CODEWITHSUNDEEP
phpmysqlsql
Welsh
Welsh

Reputation: 39

Display Null Elements

I have a table for students (containing a foreign key referencing school) and a table for schools.

I would like display list the Schools in London and total students in each school.

SQL Code is:

SELECT
sc.id_school,
sc.name,
count(*) as count
FROM
students as st
INNER JOIN
schools as sc
ON
sc.id_school=st.id_school
WHERE
sc.city='London'
GROUP BY
sc.name

The result is:

id_school   name    count
2   Gateway     4
3   St Peters   3

The result however does not display Schools in London that do not have listed students.

I would want the result to show:

id_school   name    count
2   Gateway     4
7   Manchels    0
1   Rowers      0
3   St Peters   3
4   St Johns    0

Please assist.

Upvotes: 0

Views: 48

Answers (4)

PlantTheIdea
PlantTheIdea

Reputation: 16359

While @MKhalidJunaid provided the correct answer (I already upvoted), I thought I'd offer another answer that more accurately reflects the data but also should be more performant.

SELECT sc.id_school AS School_ID
    ,sc.name AS School_Name
    ,COUNT(st.name) AS Student_Count
FROM schools AS sc
LEFT OUTER JOIN students AS st ON sc.id_school = st.id_school
WHERE sc.city = 'London'
GROUP BY sc.id_school,sc.name

By making everything ANSI-compliant (LEFT OUTER JOIN rather than just LEFT JOIN, complete GROUP BY statement), as well as making schools the driver of the query rather than students, you should get the same result set but with some performance gains.

Upvotes: 1

Mubo
Mubo

Reputation: 1070

Try to Group by the schoolID

SELECT
    sc.id_school,
    sc.name,
    count(*) as count
    FROM
    students as st
    INNER JOIN
    schools as sc
    ON
    sc.id_school=st.id_school
    WHERE
    sc.city='London'
    GROUP BY
    sc.name, sc.id_school;

Upvotes: 0

matthijs
matthijs

Reputation: 479

You can use LEFT JOIN to display all the rows 'on the left' even if they have no entry in the column 'count'. Same goes for RIGHT JOIN. OUTER JOIN displays all combinations. To display '0' instead of NULL:

IFNULL(count, 0)

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need to use RIGHT join for schools table, so even if there are no students in london schools they will be returned 

SELECT
    sc.id_school,
    sc.name,
    count(*) as count
    FROM
    students as st
    RIGHT JOIN
    schools as sc
    ON
    sc.id_school=st.id_school
    WHERE
    sc.city='London'
    GROUP BY
    sc.name

Upvotes: 2

Related Questions