mysql - 在单个查询中获取多个结果

我需要知道学生-学生证，名字，姓氏。那些还没有在特定的礼堂看到过活动。我必须按姓氏升序列出这些学生（通过一个查询）。两次运行查询，一次运行显示礼堂“伦敦剧院”，第二次显示“布伦特伍德音乐厅”的结果。

我不知道这第一轮和第二轮意味着什么。另外，请检查以下内容是否可以首次运行。我正在考虑创建一个视图并存储第一个运行，并使用“ Brentwood Hall”编写相同的查询并将其存储在第二个视图中。但是，如何在同一查询中显示2个视图呢？

SELECT op.StudentID
     , s.FirstName
     , s.LastName
  FROM auditorium a
  JOIN audievent e
    ON a.auditoriumID = e.AuditoriumID
  JOIN reserver r
    ON e.EventID = r.EventID
  JOIN OrderProcessor op
    ON r.OrderID = op.OrderID
  JOIN Student s
    ON op.StudentID = s.StudentID
 WHERE op.StudentID NOT IN ( SELECT DISTINCT op.StudentID
                                        FROM OrderProcessor op
                                        LEFT
                                        JOIN reserver r
                                          ON op.OrderID = r.OrderID
                                        LEFT
                                        JOIN AudiEvent e
                                          ON r.AuditoriumID = e.AuditoriumID
                                       WHERE e.EventID NOT IN ( SELECT EventID FROM reserver)
                            )
   AND a.AuditoriumName = 'London Theatre'
 GROUP
    BY op.StudentID;

输出：-

StudentID FirstName LastNameID1 Andy HallID2 Andy HallsID3 Mush PetersID4 Garry Hiegl

输入：-

礼堂

AudiID AudiNameAudi01 London TheatreAudi02 Brentwood HallAudi03 County Hall

奥迪赛事

EventID AudiID1 Audi012 Audi013 Audi014 Audi015 Audi016 Audi017 Audi018 Audi019 Audi0110 Audi0111 Audi0212 Audi0213 Audi0214 Audi0215 Audi0216 Audi0317 Audi0318 Audi0319 Audi0320 Audi03

储备金

ReserverID OrderID AudiID EventID1 1 Audi01 12 1 Audi01 23 2 Audi01 34 2 Audi01 45 2 Audi02 146 3 Audi02 157 3 Audi02 118 4 Audi03 189 4 Audi03 1910 4 Audi03 20

OrderProcessor

OrderID StudentID1 ID012 ID023 ID034 ID04

学生

StudentID FirstName LastNameID1 Andy HallID2 Andy HallsID3 Mush PetersID4 Garry Hiegl

最佳答案

正如@wildplasser提到的，NOT EXISTS非常适合：

SELECT studentId, student.firstName, student.lastName
FROM Student
WHERE NOT EXISTS (SELECT 1
                  FROM OrderProcessor
                  JOIN Reserver
                    ON Reserver.orderId = OrderProcessor.orderId
                  JOIN AudiEvent
                    ON AudiEvent.eventId = Reserver.eventId
                  JOIN Auditorium
                    ON Auditorium.audiId = AudiEvent.audiId
                       AND Auditorium.audiName = 'London Theatre'
                  WHERE OrderProcessor.studentId = Student.studentId)

（工作SQL Fiddle example）
让未看过'Brentwood Hall'事件的学生进行必要的更改留给读者练习。