How to use a where clause with a join-CodePudding

Table Student

Id	Name
1	Name
2	Name

Table Classes

Id	Name	Number
1	class1	2
2	class1	3

and a join table

StudentId	ClassId	Income
1	1	5
2	2	6

and the query

select sum(scr.Income) 
from Student s 
left join StudentClassRelation scr on scr.classId = s.Id 
left join Class c on c.Id = scr.classId and c.Number > 4

I want to move c.Number > 4 on the second line in order to receive only the income from classes with number greater than 4. I cannot change the query significantly because it is a part of a bigger one. I need to filter StudentClassRelation somehow

CREATE TABLE Student
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
);

CREATE TABLE Class
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Number int
);

CREATE TABLE StudentClassRelation
(
    Income int,
    StudentID INT NOT NULL,
    ClassID INT NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Student(Id),
    FOREIGN KEY (ClassID) REFERENCES Class(Id),
    UNIQUE (StudentID, ClassID)
);

INSERT INTO Class (Id, Name, Number) 
VALUES (1, '1', 1), (2, '5', 5)

INSERT INTO Student (Id, Name) 
VALUES (1, '1'), (2, '5')

INSERT INTO StudentClassRelation (StudentID, ClassID, Income) 
VALUES (1, 1, 10), (2, 1, 20), (2, 2, 5)

CodePudding user response：

Here a workaround (2options) if u dont want to change the rest of your query

select sum(scr.Income) from Student s 
left join StudentClassRelation scr on scr.classId = s.Id    
    and EXISTS(select top 1 1 from Class where Class.Id = scr.classId and Class.Number > 4)

--> 5

select sum(scr.Income) from Student s 
left join StudentClassRelation scr on scr.classId = s.Id    
    and (select top 1 Class.Number from Class where Class.Id = scr.classId) > 4

--> 5

Is it working as expected?

EDIT: my bad, the solution provided by @GarethD is way better (using inner join instead of left join)

CodePudding user response：

The number of your class is only present in the Class table. That means that you cannot use this column before joining the Class table. In the StudentClassRelation there is no reference to the number of the class, unfortunately.

CodePudding user response：

To keep the join to StudentClassRelation as a LEFT JOIN and apply a filter based on a column in the class table, you could use LEFT JOIN to a subquery that uses an INNER JOIN on Class, e.g.

SELECT  SUM(scr.Income)
FROM    Student AS s
        LEFT JOIN
        (   SELECT  scr.StudentId, scr.Income
            FROM    StudentClassRelation AS scr
                    INNER JOIN Class AS c
                        ON c.Id = scr.ClassId
            WHERE   c.Number > 4
        ) AS scr
            ON scr.StudentId = s.Id;

You can however rewrite this in a less verbose way as follows:

SELECT  SUM(scr.Income)
FROM    Student AS s
        LEFT JOIN (StudentClassRelation AS scr
            INNER JOIN Class AS c
                ON c.Id = scr.ClassId
                AND c.Number > 4)
            ON scr.StudentId = s.Id;

The execution plans of the two are exactly the same, which is "better" would be entirely personal preference as to which you find more readable. You spend more time reading code than writing it, so the least verbose method does not equate to the best method.

Also worth noting, that if this was the entire query, there is no difference at all from simply using INNER JOIN throughout

SELECT  SUM(scr.Income)
FROM    Student AS s
        INNER JOIN StudentClassRelation AS scr
            ON scr.classId = s.Id
        INNER JOIN Class AS c
            ON c.Id = scr.classId
            AND c.Number > 4;

But since you have mentioned that this is part of a larger query, I will assume that there is more to it than just the posted sample, and there is in fact a need for the LEFT JOIN to StudentClassRelation, e.g. If you were to do something like:

SELECT  s.Id, Income = SUM(scr.Income)
FROM    Student AS s
        LEFT JOIN (StudentClassRelation AS scr
            INNER JOIN Class AS c
                ON c.Id = scr.ClassId
                AND c.Number > 4)
            ON scr.StudentId = s.Id
GROUP BY s.Id;

This would yield different results to the version with an INNER JOIN to both tables

SELECT  s.Id, Income = SUM(scr.Income)
FROM    Student AS s
        INNER JOIN StudentClassRelation AS scr
            ON scr.classId = s.Id
        INNER JOIN Class AS c
            ON c.Id = scr.classId
            AND c.Number > 4
GROUP BY s.Id;