У меня есть этот код и его временные таблицы, чтобы вы могли его запустить.
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
Это должно помочь вам начать:
-- 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
Вы наверняка хотите что-то в этом роде. Это даст вам значения викторины и возвращает 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
это
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
Мое взятие на себя - похоже на ответ Патрика с крестом.
Полный образец доступен в 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
Это даст вам каждого ученика вместе с викториной, которую они не приняли.