MySQL – How to Use SELECT Query Inside ON CLAUSE in LEFT OUTER JOIN

join;MySQLselect

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->user_id = $wpdb->get_row("SELECT user_id FROM abc_comments ORDER BY comment_posted DESC LIMIT 1" )->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 .

Best Answer

"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)).)

Related Question