How to write the sql statement for the below query?

Question

i have a table(lab_schedule) columns as below,

c_code    labclass      day
EEI4163   2019-04-09   Sunday
EEI4362   2019-03-05   Monday
EEI4362   2019-04-07   Tuesday
EEI4456   2019-05-06   Wednesday

I want view the course codes which do not have lab class on 2019-03-05

select c_Code,labclass 
from lab_schedule 
where labclass != "2019-03-05" group by c_code,labclass;

i tried to get the result by using the group by syntax in sql. but I expected only EEI4163, EEI4456 but it shows EEI4362 too which is in 2019-04-07.

But EEI4362 course has a lab class on 2019-03-05.

Answer 1

You can use a self-outer join as an alternative solution like below:

select l1.c_Code, l1.labclass
from lab_schedule l1
left outer join lab_schedule l2 on l1.c_Code = l2.c_Code and l2.labclass = '2019-03-05'
where l2.c_Code is null
group by l1.c_Code, l1.labclass

Edit: If you wanna see only c_code, remove l1.labclass from "select" and "group by" statements.