una - modificar columna sql server

Forma correcta de unirse a SQL si el otro parámetro es nulo

Question

Tengo este código y sus tablas temporales para que pueda ejecutarlo.

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

Así que, como puedes ver, lebron james toma la prueba nba top 10 de 3 puntos para tiradores y stephen curry toma la prueba nba 50 de mayor jugador.

Todo lo que quiero es obtener lo que ellos no tomaron, por ejemplo, LeBron no ha tomado la prueba de 50 jugadores más grandes, así que lo que quiero es así.

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

Quiero 2 parámetros, el id de lebron y el id del cuestionario para saber que lebron o stephen aún no lo han tomado, pero ¿cómo lo haré si el valor del student_id sigue siendo nulo?

Mi intento:

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

Esta

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

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

Editar: cambio de código a cross join explícita cross join lugar de implícita, dejando los dos aquí para su comparación

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

vs.

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

Answer 2

Esto debería comenzar:

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

Mi opinión sobre esto es similar a la respuesta de Patrick con una combinación cruzada.

Muestra completa disponible en 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;

Answer 4

Seguro que quieres algo en esta línea. Esto le dará los valores del cuestionario y devolverá NULL para el alumno y quiz_details cuando no haya datos coincidentes.

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

Answer 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

Esto le dará a cada estudiante junto con el cuestionario que no han tomado.