SQL correct way of joining if the other parameter is null

Question

I have this code and its temporary tables so you can run it.

create table #student (     id int identity(1,1),     firstname varchar(50),     lastname varchar(50) )  create table #quiz (     id int identity(1,1),     quiz_name varchar(50) )  create table #quiz_details (     id int identity(1,1),     quiz_id int,     student_id int )  insert into #student(firstname, lastname) values ('LeBron', 'James'), ('Stephen', 'Curry')  insert into #quiz(quiz_name) values('NBA 50 Greatest Player Quiz'), ('NBA Top 10 3 point shooters')  insert into #quiz_details(quiz_id, student_id) values (1, 2), (2, 1)   drop table #student drop table #quiz drop table #quiz_details 

So as you can see lebron james takes the quiz nba top 10 3 point shooters quiz and stephen curry takes the nba 50 greatest player quiz.

All I want is to get the thing that they didn't take yet for example LeBron hasn't taken the 50 greatest player quiz so what I want is like this.

id   quiz_name                    firstname  lastname ---------------------------------------------------- 1    NBA 50 Greatest Player Quiz  NULL       NULL  

I want 2 parameters, the id of lebron and the id of the quiz so that I will know that lebron or stephen hasn't taken it yet, but how would I do that if the value of the student_id is still null?

My attempt:

select     QD.id,     Q.quiz_name,     S.firstname,     S.lastname from      #quiz_details QD inner join      #quiz Q on Q.id = QD.quiz_id inner join      #student S on S.id = QD.student_id 

Answer 1

This should get you started:

-- filter out the student and quiz you want DECLARE @qid INT = 1 DECLARE @sid INT = 1  SELECT *  FROM #student AS s INNER JOIN #quiz AS q  -- you want the quiz  ON 1=1 LEFT OUTER JOIN #quiz_details AS qd  -- left join here to get result where rows not found  ON qd.id = q.id   AND qd.student_id=s.id WHERE s.id = @sid  AND q.id = @qid  AND qd.id IS NULL -- only return quizes not taken 

Answer 2

Pretty sure you want something along these lines. This will give you the quiz values and return NULL for the student and quiz_details when there is no matching data.

select * from #quiz q left join #quiz_details qd on q.id = qd.quiz_id left join #student s on s.id = qd.student_id