mysql join return all records from left table

Question

table comment report

CREATE TABLE $comment_report_table ( 
            `comment_id` int(11) unsigned DEFAULT NULL,
            `user_id` int(11) unsigned DEFAULT NULL,
            `reason` varchar(100) DEFAULT NULL,
            INDEX `comment_id` (`comment_id`)

comment_id   user_id  reason
11          22       foo
11          3        aks
2           7        tettre

table comments

CREATE TABLE $comments_table ( 
            `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
            `media_id` int(11) unsigned DEFAULT NULL,
            `user_id` int(11) unsigned DEFAULT NULL,
            `c_date` datetime,
            `comment` longtext DEFAULT NULL,
            PRIMARY KEY (`id`),
            INDEX `media_id` (`media_id`)

id (primary)   user_id  comment      c_date
02              3        ddwww       2023-01
09              5        www         2023-02
10              22       ee          2024-11
11              3        rrrrr       2024-02
12              7        gg          2025-01

query:

"SELECT crt.comment_id, crt.user_id as report_user_id, crt.reason as report_reason, ct.id, ct.comment, ct.user_id, ct.c_date
                FROM $comment_report_table as crt 
                LEFT JOIN $comments_table ct on crt.comment_id = ct.id 
                GROUP BY crt.comment_id
                ORDER BY ct.c_date DESC

this will return only 2 results from report table:

comment_id   user_id  reason
11          3        aks
2           7        tettre

I want all results from report table. It seems this skips results from report table with same comment_id

mySQL extends the group by so you don't have to group by all non-aggregated values. This is not standard SQL. This means the system is free to choose each value not aggregated, and not in the group by. Either eliminate the group by or add all needed key fields to the group by (in this case I think id, and user_Id is the PK for comment_Reports but you don't specify the Pk for that table.) — xQbert
– xQbert, Commented Apr 7 at 17:32
That statement should not have executed. It doesn't make sense to put non-aggregated column that are not in the GROUP BY. — Eric
– Eric, Commented Apr 7 at 17:35
It can be illustrated clearly from your example. Since you are grouping it by crt-comment_id, there will be only one row of comment_id, so in this case, which user_id and reason will be selected? — Eric
– Eric, Commented Apr 7 at 17:40
"It seems this skips results from report table with same comment_id". That's what GROUP BY does. It groups data by whatever column(s) you put it there. In this case, there will be a row for each comment_id. That's why your query should not even have executed.dev.mysql.com/doc/refman/8.4/en/group-by-modifiers.html — Eric
– Eric, Commented Apr 7 at 17:43
Docs @Eric; mysql extends the group by unless feature is disabled. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic... — xQbert
– xQbert, Commented Apr 7 at 18:11

vinsentos · Accepted Answer · 2025-04-07 16:25:59Z

2

The issue you’re facing is due to the GROUP BY clause in your SQL query.

This line collapses multiple rows from the left table ($comment_report_table) into one row per comment_id, which removes duplicates — so only one report per comment_id is shown, even though you have multiple reports for the same comment_id.

SELECT 
  crt.comment_id, 
  crt.user_id AS report_user_id, 
  crt.reason AS report_reason, 
  ct.id, 
  ct.comment, 
  ct.user_id, 
  ct.c_date
FROM $comment_report_table AS crt 
LEFT JOIN $comments_table AS ct 
  ON crt.comment_id = ct.id 
ORDER BY ct.c_date DESC;

answered Apr 7 at 16:25

vinsentos

3713 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Toniq Apr 7 at 17:50

thsi works, but I am trying to left join another table to this now and this is causing less results. I guess not all comments have votes so this query is not returning all: pastecode.io/s/2hgi6uzh

xQbert Apr 7 at 18:15

@Toniq add a dB fiddle with sample data expected results to your original question. it will help us understand the problem. As it stands, this should not reduce records unless there is bad data; incomplete joins. or joins on non PK/FK relationships. Or if you have a where clause on a table on teh right side of a left join; which would negate the left join

vinsentos Apr 7 at 21:05

If adding a LEFT JOIN (like votes) reduces results, check for GROUP BY collapsing rows or NULLs from unmatched joins. Try using a subquery: LEFT JOIN ( SELECT comment_id, SUM(vote) AS total_votes FROM $votes_table GROUP BY comment_id ) vt ON ct.id = vt.comment_id Then: COALESCE(vt.total_votes, 0) to default missing votes to 0.

ysth Apr 8 at 5:36

Seems likely they just have the join in the wrong order (plus the bad group by)

Collectives™ on Stack Overflow

mysql join return all records from left table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related