mysql - 加入查询时出现问题

Question

我正在努力吸引那些没有参加过任何特定研讨会的学生。然而，这是行不通的。

SELECT Student.FirstName,Student.LastName 
from Student WHERE NOT EXISTS(
                        SELECT 1 from Seminar A
                        LEFT OUTER JOIN Individual B
                        ON A.SeminarID=B.IndividualID AND    B.LastName='Johns'
                        LEFT OUTER JOIN Group C
                        ON A.SeminarID=C.GroupID AND   C.GroupName='Koir Media Pvt Ltd'
                        LEFT OUTER JOIN OrderProcessor G 
                        ON F.OrderID=G.OrderID
                        INNER JOIN EventSeminar D
                        ON D.SeminarID=A.SeminarID
                        INNER JOIN AudiEvent E
                        ON D.EventID=E.EventID
                        INNER JOIN Reserver  F
                        ON E.EventID=F.EventID
                        INNER JOIN Student H
                        ON G.StudentID=H.StudentID
                        where G.StudentID=H.StudentID);

输出 -

FirstName   LastName
  Matt       Johnson
 Mattew      John
  Shane      Watson

输入 -

学生

StudentID   FirstName   LastName
1           Matt        Johnson
2           Mattew      John
3           Shane       Watson
4           Bill        Nielsen
5           Garry       Stevenson

订单处理器

OrderID StudentID
O01         1
O02         2
O03         3
O04         4
O05         5
O06         4
O07         3
O08         2
O09         1
O10         5
O11         2
O12         3
O13         1
O14         4
O15         1
O16         1

服务器

ReserverID  OrderID AudiID  EventID
R01           O01     1       E01
R02           O02     1       E01
R03           O03     1       E01
R04           O04     1       E02
R05           O05     1       E02
R06           O06     1       E02
R07           O07     1       E02
R08           O08     1       E03
R09           O09     2       E03
R10           O10     2       E03
R11           O11     2       E03
R12           O12     2       E04
R13           O13     2       E04
R14           O14     2       E04
R15           O15     2       E05
R16           O16     2       E05

奥迪事件

EventID AudiID  
  E01     1 
  E02     1 
  E03     2 
  E04     2 
  E05     2 

事件研讨会

EventID SeminarID   
  E01     S01   
  E02     S02   
  E03     S03   
  E04     S04   
  E05     S05   
  E01     S06   
  E02     S07   
  E03     S08   
  E04     S09   
  E05     S10   

研讨会

SeminarID   IndividualID    GroupID
   S01        I01            null
   S02        I02            null
   S03        I03            null
   S04        I04            null
   S05        I05            null
   S06        null           G01
   S07        null           G02
   S08        null           G03
   S09        null           G04
   S10        null           G05

个人

IndividualID    FirstName   LastName
    I01          Steven      Johns
    I02          Barkha      Dutt
    I03          Pronoy      Roy
    I04          Alan        Wilkins
    I05          Harsha      Bhogle

组

GroupID GroupName   
  G01   Koir Media Pvt Ltd  
  G02   Headlines Today 
  G03   The Age 
  G04   TOI 
  G05   HT  

您能帮我看看我的查询有什么问题吗？我的查询没有给我正确的结果...

Answer 1

好的。试试这个:

“未参加任何特定研讨会的学生”

Select s.FirstName, s.LastName, StudentSeminars.SeminarCount
From Student s
LEFT JOIN (SELECT o.StudentID, count(r.EventID) as SeminarCount
           FROM OrderProcessor o
           INNER JOIN Reserver r on r.OrderID = o.OrderID
           INNER JOIN EventSeminar e on e.EventID = t.EventID
           INNER JOIN Seminar s on s.SeminarID = e.SeminarID
           LEFT JOIN Individual i on i.IndividualID = s.IndividualID
           LEFT JOIN Group g on g.GroupID = s.GroupID
           WHERE i.LastName='Johns' OR g.GroupName='Koir Media Pvt Ltd'
           GROUP BY o.StudentID ) StudentSeminars
    ON StudentSeminars.StudentId = s.StudentId
WHERE StudentSeminars.SeminarCount IS NULL

子选择获取所有预订研讨会席位的学生的列表，并计算研讨会的数量。然后，主查询从所有学生的列表开始，并连接到子选择以找出哪些学生没有参加任何研讨会!

我假设保留表中存在记录意味着学生“参加了特定的研讨会”。我还假设并非所有事件都是研讨会，因此加入 EventSeminar 表以确保我们只计算研讨会。