CODEWITHSUNDEEP
sqlsql-serversql-server-2008isnullcorrelated-subquery
αмιѕнα к.
αмιѕнα к.

Reputation: 25

Unable to find a solution or workaround

SQL Server 2008 and above.

Suppose I have a Database table named 'Teachers' which contains Details of Teachers in a school and another Table named 'Schedules' which contains their Schedules. Sometimes if I require to automatically assign few hours to a teacher/teachers on a particular day randomly like when one teacher is absent then another teacher can take that available time.

Like here :

Declare @hrs numeric(24, 6) = 3

SELECT T_Code 
FROM Teachers 
LEFT JOIN Schedules S
WHERE (SELECT 8 - ISNULL(SUM(S_HOURS),0) AS AVLHRS
        FROM  Schedules 
        WHERE S_Teacher = S.S_Teacher)  < @hrs
ORDER BY ISNULL(S_HOURS,0), NEWID()

But the problem here is that only those Teachers who are scheduled are calculated so i am not getting list of all the Teachers available. Like suppose I have a new teacher who is not even scheduled also want to order it in the desc order of hours left by that teacher. Then this won't show that Teacher but I also want that teacher to be seen. I am unable to find the work around.

PS. This is not the complete code and it checks many other conditions but this is the only condition causing problem and I can not show the complete code.

Upvotes: 1

Views: 79

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Use LEFT JOIN this will bring the all Teacher Records 

SELECT T_Code 
FROM Teachers 
LEFT JOIN Schedules S ON Teachers.ID = S.TeacherID
AND (SELECT 8 - ISNULL(SUM(S_HOURS),0) AS AVLHRS
       FROM  Schedules 
       WHERE S_Teacher = S.S_Teacher)  < @hrs
ORDER BY ISNULL(S_HOURS,0), NEWID()

Updated :

SELECT T_Code,
       ISNULL(TmpTable.AVLHRS,0)  AVLHRS  
FROM Teachers 
LEFT JOIN Schedules S ON Teachers.ID = S.TeacherID
OUTER APPLY(
   SELECT 8 - ISNULL(SUM(S_HOURS),0) AS AVLHRS
   FROM  Schedules 
   WHERE S_Teacher = S.S_Teacher
)TmpTable
WHERE  TmpTable.AVLHRS < @hrs
ORDER BY AVLHRS

Upvotes: 2

jogoe
jogoe

Reputation: 324

To display all teachers, whether or not they have a schedule, you have to use a LEFT JOIN. A LEFT JOIN displays all the data of the left table (in your case teachers) even if it doesn't have a value on the right table (in your case schedules).

In your code this looks like this:

SELECT T_Code from Teachers LEFT JOIN Schedules S
WHERE (SELECT 8 - ISNULL(SUM(S_HOURS),0) AS AVLHRS
FROM  Schedules WHERE S_Teacher = S.S_Teacher)  < @hrs
ORDER BY ISNULL(S_HOURS,0), NEWID()

Upvotes: 1

Related Questions