mysql - MYSQL:选择其中的/和/或带有两个表

我正在尝试使以下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)
);

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

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

最佳答案

尝试联接两个表并使用“位置”：

 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