MYSQL:选择其中/和/或两个表

Question

我正在尝试让以下 SQl 正常工作:

SELECT * FROM tc_appointment as tcAppointment, tc_message as tcMessage
WHERE tcAppointment.counsellor_id = 502
AND 
(
    (tcAppointment.tc_message_id = tcMessage.id AND tcMessage.marked_read = false AND tcMessage.sender_id != 502)
    OR
    (tcAppointment.cancelled = true AND tcAppointment.cancelled_acknowledged = false)
    OR 
    (tcAppointment.confirmed = false)
);

表中唯一的tc_appointment 条目中的tcAppointment.tc_message_id 为空。我试图让它以 counsellor_id = 502 的形式返回，第二个 OR 语句为 true

由于 tc_message as tcMessage 子句和第一个 AND/OR 语句，我似乎陷入了困境，但我不确定为什么。谁能指出我正确的方向吗？

Answer 1

尝试连接两个表并使用“Where”:

 SELECT * FROM tc_appointment as tcAppointment 
    LEFT JOIN tc_message as tcMessage 
    ON 
    ((tcAppointment.tc_message_id = tcMessage.id AND tcMessage.marked_read = false AND tcMessage.sender_id != 502)
    OR
        (tcAppointment.cancelled = true AND tcAppointment.cancelled_acknowledged = false)
        OR 
        (tcAppointment.confirmed = false)
) 

    WHERE tcAppointment.counsellor_id = 502