outer join with not in and for each in sql

user_14699485 0
Viewed 54

This one has only one table with three columns student, lect and score. for each lecture I need to find the students who have not got any score.

I have written the below query which uses outer joins, but it can do so only for one lect at a time.

Eg: see below I passed 'L02'

How do I get this working for all the lect values as in (L01,L02,L03...etc)

select distinct * from 
(
select  distinct Student from import1
where lect ='L02'
)i1 
right outer join
(select  distinct Student from import1) i2
on i1.Student=i2.Student 
where i1.Student is null

output of above is

output of query

This works for L02. But, how do I modify above to include for all lect values without hardcoding the values of lect?

Sample data from table:

sample data

sql
1 Answers

Need a dataset of all possible student/lecture pairs. If the one table contains all the students and lectures that need to be considered, this dataset can be built with:
SELECT Student, Lect FROM (SELECT DISTINCT Student FROM import1) AS S, (SELECT DISTINCT Lect FROM import1) AS L

Otherwise, need a table of all students and a table of all lectures then query:
SELECT Student, Lect FROM Students, Lectures

Now join that query to the scores table with compound link on both identifier fields and use appropriate filter criteria.

SELECT Query1.Student, Query1.Lect, import1.Score
FROM Query1 LEFT JOIN import1 
ON (Query1.Lect = import1.Lect) AND (Query1.Student = import1.Student)
WHERE Score Is Null;

Tested with an Access database.

user_76071900
Related