Блог

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

 document_category(document_category_id, document_category)
document_type(document_type_id, document_category.document_category_id, document_type)
student(student_id, f_name, l_name, ...other_columns)
student_document(id, student.student_id, document_type.document_type_id, file)
 

document_category, document_type, Студенты и студенческий документ

В таблице student_document хранятся загруженные документы. Я хочу, чтобы запрос отображал список документов, которые студент не загружал.

Я пытался

 (SELECT document_type FROM document_category JOIN document_type ON document_category.document_category_id = document_type.document_category_id
) LEFT JOIN(SELECT FILE FROM student_document JOIN student ON student.student_id = student_document.student_id) ON document_type.document_type_id = student_document.document_type_id
 

И я получаю ошибку

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN(
    SELECT FILE
FROM
    student_document
JOIN student ON stud...' at line 8
 

Я также попробовал это

 SELECT * FROM document_type A LEFT JOIN student_document B ON A.document_type_id = B.document_type_id WHERE B.document_type_id is null
 

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

и последнее, что я пробовал, это

 SELECT student.email, student_document.file, document_type, document_category FROM student, document_type, document_category, student_document WHERE NOT EXISTS(SELECT * FROM student_document WHERE student_id = 'M054/T19' AND document_type_id ='20') AND student.student_id = student_document.student_id AND document_type.document_category_id = document_category.document_category_id
 

Что дает мне Нежелательное, и это не то, чего я хочу.

 DECLARE @dc TABLE(dc_id Int, ctg VarChar(30));
INSERT INTO @dc VALUES (3,'Admission'),(5,'Payment');
DECLARE @dt TABLE (dt_id Int, dc_id Int, dtp VarChar(30));
INSERT INTO @dt VALUES (27,3, 'Admission Offer'),
(28,3,'Acceptance Letter');
DECLARE @s TABLE(s_id Int, f_name VarChar(30))
INSERT INTO @s VALUES (1, 'Marco'), (2, 'Mike')
DECLARE @sd TABLE (sd_id Int, s_id Int, dt_id Int, [file] VarChar(30))
INSERT INTO @sd VALUES (10,1,27,'File01');

SELECT dc.dc_id, ctg, dt.dt_id, dt.dtp, s.s_id, f_name FROM @dc dc 
JOIN @dt dt ON dc.dc_id = dt.dc_id
CROSS APPLY @s s

-- All expected documents
dc_id   ctg dt_id   dtp s_id    f_name
3   Admission   27  Admission Offer 1   Marco
3   Admission   27  Admission Offer 2   Mike
3   Admission   28  Acceptance Letter   1   Marco
3   Admission   28  Acceptance Letter   2   Mike

-- Provided documents
    SELECT dc.dc_id, ctg, dt.dt_id, dt.dtp, s.s_id, f_name FROM @dc dc 
    JOIN @dt dt ON dc.dc_id = dt.dc_id
    JOIN @sd sd ON sd.dt_id = dt.dt_id
    JOIN @s s ON s.s_id = sd.s_id 

dc_id   ctg dt_id   dtp s_id    f_name
3   Admission   27  Admission Offer 1   Marco

-- Subtracting Set from Set
SELECT dc.dc_id, ctg, dt.dt_id, dt.dtp, s.s_id, f_name FROM @dc dc 
JOIN @dt dt ON dc.dc_id = dt.dc_id
CROSS APPLY @s s
EXCEPT
SELECT dc.dc_id, ctg, dt.dt_id, dt.dtp, s.s_id, f_name FROM @dc dc 
JOIN @dt dt ON dc.dc_id = dt.dc_id
JOIN @sd sd ON sd.dt_id = dt.dt_id
JOIN @s s ON s.s_id = sd.s_id 

dc_id   ctg dt_id   dtp s_id    f_name
3   Admission   27  Admission Offer 2   Mike
3   Admission   28  Acceptance Letter   1   Marco
3   Admission   28  Acceptance Letter   2   Mike
 

Этот код работал идеально. Первый запрос, прежде WHERE NOT EXISTS чем получить список всех необходимых документов и список студентов, содержит подробный список документов, которые студент должен загрузить. Вторая часть извлекает список документов, загруженных студентом.

Таким образом, запрос извлекает список необходимых документов, которых нет в списке загруженных документов.

 SELECT sp.student_id, do.document_type_id, do.document_type 
FROM student_profile sp 
CROSS JOIN document_type do 
WHERE NOT EXISTS (SELECT sd.student_id, sd.document_type_id, d.document_type FROM student_document sd 
INNER JOIN document_type d ON sd.document_type_id = d.document_type_id 
WHERE do.document_type_id = sd.document_type_id 
AND sp.student_id = sd.student_id );