Jacques Jacques - 1 month ago 10
MySQL Question

MySQL - Jointure with OR condition in 2 bloc with AND condition

This query works fine with most server but does not work well with some other. It cause CPU overload or empty result.

SELECT
M.id,
M.sender,
M.recipient,
M.date,
M.read,
U.ID
FROM
msg M,
users U
WHERE
(M.recipient='".$user_login."' and
M.deleted!=1 and
U.user_login=M.sender)
or
(M.sender='".$user_login."' and
M.deleted!=2 and
U.user_login=M.recipient)
ORDER BY M.date DESC


I have change to this but same issue :

SELECT
M.id,
M.sender,
M.recipient,
M.date,
M.read,
U.ID
FROM
msg M
LEFT JOIN
users U
ON
(M.recipient='".$user_login."' and
M.deleted!=1 and
U.user_login=M.sender)
or
(M.sender='".$user_login."' and
M.deleted!=2 and
U.user_login=M.recipient)
ORDER BY M.date DESC


I think the problem is the jointure clause with 2 cases.

I don't think there can be a case problem with "id" and "ID".

Of course I can make 2 queries but surely there is a way to do this with a single query.

Answer

An OR in a JOIN condition sounds like a horrible idea. Normally a UNION would be the better solution:-

SELECT 
    M.id,
    M.sender,
    M.recipient,
    M.date AS msg_date,
    M.read,
    U.ID
FROM msg M
INNER JOIN users U
ON U.user_login=M.sender
WHERE M.recipient='".$user_login."'
AND M.deleted!=1
UNION
SELECT 
    M.id,
    M.sender,
    M.recipient,
    M.date AS msg_date,
    M.read,
    U.ID
FROM msg M
INNER JOIN users U
ON U.user_login=M.recipient
WHERE M.sender='".$user_login."'  
AND M.deleted!=2 
ORDER BY msg_date DESC