SQL правильный способ соединения, если другой параметр равен нулю

У меня есть этот код и его временные таблицы, чтобы вы могли его запустить.

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

Так как вы можете видеть, что lebron james берет викторину nba top 10 3-х точечных стрелков, а stephen curry берет самую большую викторину игрока nba 50.

Все, что я хочу, это получить то, чего они еще не взяли, например, LeBron не взял 50 лучших викторинов игроков, поэтому я хочу, чтобы это было так.

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

Я хочу 2 параметра, идентификатор lebron и идентификатор викторины, чтобы я знал, что lebron или stephen еще не приняли его, но как бы это сделать, если значение student_id все равно null?

Моя попытка:

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

Ответ 1

Это должно помочь вам начать:

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

Ответ 2

Вы наверняка хотите что-то в этом роде. Это даст вам значения викторины и возвращает NULL для ученика и quiz_details, когда нет соответствующих данных.

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

Ответ 3

это

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)

дам тебе

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

Изменение: изменил код на явное cross join а не подразумевалось, оставив оба здесь для сравнения

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

против

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

Ответ 4

Мое взятие на себя - похоже на ответ Патрика с крестом.

Полный образец доступен в 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;

Ответ 5

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

Это даст вам каждого ученика вместе с викториной, которую они не приняли.