when - sql server if null




Manière correcte de joindre SQL si l'autre paramètre est null (4)

J'ai ce code et ses tables temporaires afin que vous puissiez l'exécuter.

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

Donc, comme vous pouvez le voir, lebron james répond au quiz nba top 10 du quiz shooter à 3 points et stephen curry répond au quiz nba des 50 meilleurs joueurs.

Tout ce que je veux, c'est obtenir ce qu'ils n'ont pas encore pris. Par exemple, LeBron n'a pas participé au quiz des 50 meilleurs joueurs. Ce que je veux, c'est comme ça.

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

Je veux 2 paramètres, l'identifiant de lebron et l'identifiant du quiz pour que je sache que lebron ou stephen ne l'ont pas encore pris, mais comment le ferais-je si la valeur de student_id est toujours nulle?

Ma tentative:

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

Ce

Select Q.id , Q.quiz_name ,S.firstname, S.lastname
from 
    #quiz Q                -- cross join, returns N*K results, do not use without 
    CROSS JOIN #student S  -- where condition that limits it - SAS solution is nicer!
where not exists (select 1 from #quiz_details where quiz_id = Q.id and student_id = S.id)

te donnera

id  quiz_name                         firstname     lastname
1   NBA 50 Greatest Player Quiz       LeBron        James
2   NBA Top 10 3 point shooters       Stephen       Curry

Edit: code modifié pour une cross join explicite plutôt qu'implicite, laissant les deux ici pour la comparaison

SELECT #quiz Q, # student S           -- old implicit syntax - comma is easily missed

contre.

SELECT #quiz Q CROSS JOIN #student S  -- makes it clearer what is wanted

Cela devrait vous aider à démarrer:

-- 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

Mon point de vue - similaire à la réponse de Patrick avec une croix joindre.

Échantillon complet disponible à sqlfiddle

select
  Q.Quiz_Name Quiz
 ,S.LastName Last
 ,S.FirstName First
 ,QD.Quiz_ID
 ,QD.Student_ID
from 
/* Get a full list of ALL Test/Student combinations */
           quiz Q 
CROSS JOIN student S 
/* Join the taken tests to the combinations */
 LEFT JOIN quiz_details QD on Q.id = QD.quiz_id
                          and S.id = QD.student_id
/* Only select where no Quiz_ID exists */
WHERE QD.Quiz_ID IS NULL
ORDER BY Q.Quiz_Name, S.Lastname, S.FirstName;

select s.firstname, s.lastname, q.id as not_taken_quiz_id, q.quiz_name as not_taken_quiz_name
from #student s
left join #quiz_details qd on s.id = qd.student_id
left join #quiz q on q.id <> qd.quiz_id

Cela vous donnera chaque étudiant avec le quiz qu'ils n'ont pas pris.





sql-server