Question 397741 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Count on LEFT JOIN in SQL

I would like to know how can I get Count on LEFT JOIN in SQL.

Currently I have 3 tables :

rooms_messages

id room_id discussion_message_id created_at
1 10 101 2024-07-16 12:30:45
2 20 102 2024-07-16 12:30:50
3 10 103 2024-07-16 12:32:45
4 20 104 2024-07-16 12:34:50
5 20 105 2024-07-16 12:36:50

discussions_messages (has many discussions_replies)

id text
101 Hello
102 Test
103 Ok
104 Wow
105 Hello2

discussions_replies

id discussion_message_id text
201 103 Hello
202 103 Test

I use this query, to show the latest rooms_messages and attach the discussions_messages text. But the replies isn’t correct:

enter image description here

WITH cte AS (
    SELECT rm.id as id, rm.created_at, rm.room_id,
           rm.discussion_message_id, dm.text, count(dm.id) as replies,
    ROW_NUMBER() OVER (PARTITION BY rm.room_id ORDER BY rm.created_at DESC) rn
    FROM rooms_messages AS rm
    INNER JOIN discussions_messages AS dm ON rm.discussion_message_id = dm.id
    LEFT JOIN discussions_replies AS dr ON dm.id = dr.discussion_message_id
    GROUP BY dm.id
)

SELECT id, created_at, room_id, discussion_message_id, text, replies
FROM cte
WHERE rn = 1

I would like to show the discussions_replies attach on discussions_messages.

I think the problem is on the line:

count(dm.id) as replies,
...
INNER JOIN discussions_messages AS dm ON rm.discussion_message_id = dm.id
LEFT JOIN discussions_replies AS dr ON dm.id = dr.discussion_message_id
Tags:

2

Answers


  1. 0

    Use a second CTE to get the counts, instead of doing it in the same CTE that gets the last row.

    WITH cte AS (
    SELECT rm.id as id, rm.created_at, rm.room_id,
           rm.discussion_message_id, dm.text,
    ROW_NUMBER() OVER (PARTITION BY rm.room_id ORDER BY rm.created_at DESC) rn
    FROM rooms_messages AS rm
    INNER JOIN discussions_messages AS dm ON rm.discussion_message_id = dm.id
    LEFT JOIN discussions_replies AS dr ON dm.id = dr.discussion_message_id
), counts AS (
  SELECT room_id, IFNULL(COUNT(dr.id), 0) AS replies
  FROM rooms_messages AS rm
  INNER JOIN discussions_messages AS dm ON rm.discussion_message_id = dm.id
  LEFT JOIN discussions_replies AS dr ON dm.id = dr.discussion_message_id
  GROUP BY room_id
)

SELECT id, created_at, cte.room_id, discussion_message_id, text, replies
FROM cte 
JOIN counts ON cte.room_id = counts.room_id
WHERE rn = 1

    DEMO

    Login or Signup to reply.
  2. 0

    To make it executable remove GROUP BY dm.id and count(dm.id) as replies,; because of PARTITION BY rm.room_id it will show rows from discussions_messages and ROW_NUMBER() will count these rows per room. This is showing 3 because you have 1 message 101 in room 10 and also 1 message 103 in room 10, but you are showing room 10 twice because it has two discussion replies. Fiddle

    Your joins were fine; the logic was incorrect; you overcomplicated it with an unnecessary window function.
    Since you only need to count discussion replies per discussion messages, adjust the code (Fiddle):

    select 
    rm.id as id, 
    rm.created_at, 
    rm.room_id,
    rm.discussion_message_id, 
    dm.text, 
    count(dr.id) as replies
FROM rooms_messages AS rm
INNER JOIN discussions_messages AS dm ON rm.discussion_message_id = dm.id
LEFT JOIN discussions_replies AS dr ON dm.id = dr.discussion_message_id
group by 1,2,3,4,5

    If you want to also show the number of messages add count(distinct dm.id) as messages to your SELECT statement Fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search