How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL I need to retrieve data from several tables. For now I am getting maximum of them from

     SELECT disc.id, 
        users.user_login as 'last_active_user',
        disc.discussion_sub,
        list.todo_list_title, 
        todo.todo_title,
        count(comments.comment),   
        MAX(CAST(comments.comment_posted AS CHAR))
    FROM abc_discussions as disc
    LEFT OUTER JOIN abc_todo_list as list
    ON disc.comment_table_type = 'todo_list' AND disc.comment_table_id = list.id 
    LEFT OUTER JOIN abc_todo as todo
    ON disc.comment_table_type = 'todo_item' AND disc.comment_table_id = todo.id
    LEFT OUTER JOIN abc_users as users
    ON users.user_id = 2
    LEFT OUTER JOIN abc_comments as comments
    ON disc.id = comments.discussion_id
    GROUP BY disc.id


where $last_comment_by-&gt;user_id = $wpdb-&gt;get_row("SELECT user_id FROM abc_comments ORDER BY comment_posted DESC LIMIT 1" )-&gt;user_id;

Is there any way to make it a single query?

I tried 

SELECT a.id , 
a.discussion_sub,
b.todo_list_title,
c.todo_title,
ANY_VALUE(q.user_login) as 'last_active_user',
count(comment),
MAX(comment_posted)
FROM abc_discussions a 
LEFT JOIN abc_todo_list b
ON a.comment_table_type = 'todo_list' AND a.comment_table_id = b.id
LEFT JOIN abc_todo c 
ON a.comment_table_type = 'todo_item' AND a.comment_table_id = c.id 
LEFT JOIN (
 SELECT x.user_id as user_id, y.discussion_id as discussion_id 
    FROM abc_users x
    LEFT OUTER JOIN abc_comments y
    ON x.user_id = y.user_id ) d
 ON d.discussion_id = a.id 
LEFT OUTER JOIN abc_users q 
ON q.user_id = d.user_id
 LEFT JOIN abc_comments e 
ON a.id = e.discussion_id 
GROUP BY a.id 


But getting wrong result for user_login and comment count .
"last_active_user" sounds like

( SELECT user_login FROM users WHERE ... ORDER BY ... DESC LIMIT 1 )


count(comments.comment) sounds like

( SELECT COUNT(*) FROM abc_comments WHERE ... )


That is, get rid of the joins and replace the aggregate values by subqueries as above.

JOIN explodes the number of rows, then


You need a GROUP BY to get it back in check
COUNT counts too big a number
Items (such as user_login) that are not in GROUP BY end up with unpredictable values.
Etc.


(Yes, MAX(comment_posted), is the same as, but simpler than, MAX(CAST(comments.comment_posted AS CHAR)).)