Ways to make my left table display all its rows-CodePudding

We want to show ALL Students from the left table (Student) and their average exams score. Whether they they did any exams at all and that their name isn't in the (Score) Table.

The following SQL code almost works but it fails to bring us 1 student (1 row), the one that is not inside the (Score) table because that student didn't do any of the exams.

SELECT
    st.ID "Student ID",
    st.Name "Name",
    round(avg(scr.Score)) "Av Score"
FROM
    Student st LEFT OUTER JOIN 
    Score scr on st.ID = scr.Player
WHERE
    scr.ID < 9 -- Because records greater have been known to be false and we don't need them
GROUP BY
    st.ID

We should see all the Student names. Not really sure what am doing wrong but thank you for any advice.

CodePudding user response：

This works but i would prefer not to hardcode in the where clause of the 2nd query in this union :

select st.ID "Student ID", st.Name "Name", round(avg(scr.Score),0) "Average Score"
from Student st LEFT OUTER JOIN Score scr on st.ID = scr.Student
where scr.ID < 9
Group by st.ID
union
select st.ID "Student ID", st.Name "Name", round(avg(scr.Score),0) "Average Score"
from Student st LEFT OUTER JOIN Score scr on st.ID = scr.Student
where st.ID = 'Z'
Group by st.ID

By the way, I did switched to SQLServer 2017 using free software on the web that allows us to switched from MySQL to SQLServer 2017 ) and this newly created UNION is still working but i first had to add the "decimal" argument value of 0 for the round function.

I mean, i would prefer using the following proposed code but somehow it's not working ?

SELECT
    st.ID "Student ID",
    st.Name "Name",
    round(avg(scr.Score)) "Av Score"
FROM
    Student st LEFT OUTER JOIN 
    Score scr on st.ID = scr.Player
WHERE
    scr.ID < 9 -- Because records greater have been known to be false and we don't need them
GROUP BY
    st.ID

CodePudding user response：

All you need is to use ISNULL

SELECT
    st.ID "Student ID",
    st.Name "Name",
    round(avg(ISNULL(scr.Score, -1))) "Av Score"
FROM
    Student st LEFT OUTER JOIN 
    Score scr on st.ID = scr.Player
WHERE
    scr.ID < 9 -- Because records greater have been known to be false and we don't need them
GROUP BY
    st.ID,
    st.Name

In this case, you will see that anyone with -1 score is a person who did not take the exams. It could be useful since score of 0 could come from a very bad exam taker

Also, while this is posibble, try to avoid spaces and system words in the identifiers and aliases

CodePudding user response：

An outer join works thus: If there are matching rows, they get joined (just like with an inner join). If there are no matching rows, you still get a joined row, but the columns of the outer joined table are set to null.

You want to outer join all scores below nine. Instead you outer join all scores:

LEFT OUTER JOIN Score scr ON st.ID = scr.Player

Then you want to get rid of the higher scores with

WHERE scr.ID < 9

But in outer joined rows the scr.ID is null, and this clause dismisses these rows, too. Thus you end up with a mere inner join.

Instead outer join the scores below nine as intended:

LEFT OUTER JOIN Score scr ON st.ID = scr.Player AND scr.ID < 9