CODEWITHSUNDEEP
phpmysqlsql
Aayush
Aayush

Reputation: 3098

How To Left Join A Table Based On The Value Received From Inner Join Of The Same Query

I am trying to run the following query, and I keep getting a syntax error. The query works fine without the LEFT JOIN. How can I implement the LEFT JOIN without error?

SELECT
    m.mid, 
    m.seq, 
    m.created_on, 
    m.created_by, 
    m.body, 
    r.status,
    u.username_clean
FROM message_recipient r
INNER JOIN message m 
ON m.mid = r.mid AND m.seq = r.seq
WHERE r.uid = ".$logged_in_id." 
AND r.status in ('A', 'N')
AND r.seq = (
             SELECT 
                MAX(rr.seq)
             FROM message_recipient rr
             WHERE rr.mid = m.mid 
             AND rr.status in ('A', 'N')
            )
AND IF (m.seq=1 and m.created_by = ".$logged_in_id." , 1=0, 1=1)
ORDER BY created_on DESC
LEFT JOIN users u 
ON u.user_id = m.created_by

Upvotes: 0

Views: 49

Answers (1)

Taryn
Taryn

Reputation: 247720

The LEFT JOIN is in the wrong place. All of the JOINs need to be before the WHERE clause:

SELECT
    m.mid, 
    m.seq, 
    m.created_on, 
    m.created_by, 
    m.body, 
    r.status,
    u.username_clean
FROM message_recipient r
INNER JOIN message m 
  ON m.mid = r.mid AND m.seq = r.seq
LEFT JOIN users u 
  ON u.user_id = m.created_by
WHERE r.uid = ".$logged_in_id." 
  AND r.status in ('A', 'N')
  AND r.seq = (
               SELECT 
                  MAX(rr.seq)
               FROM message_recipient rr
               WHERE rr.mid = m.mid 
               AND rr.status in ('A', 'N')
              )
  AND IF (m.seq=1 and m.created_by = ".$logged_in_id." , 1=0, 1=1)
ORDER BY created_on DESC

The SQL clauses go in the following order:

SELECT 
FROM
JOIN
WHERE 
GROUP BY 
ORDER BY

Even if you have multiple joins they will all appear before the WHERE

Upvotes: 2

Related Questions