• 13
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

MySQL Distinct and LEFT JOIN returns duplicate

I have 2 tables in my database. One named data, another named changes.

The table's columns are

Data

------------
|id | name |
|-----------
|1  | Test |
|2  | Hello|
------------

changes

------------------------------------
|id | name | related_id | Comments |
|-----------------------------------
|1  | Test |     1      |  Example |
|2  | Hello|     2      | Example2 |
|3  | Hello|     2      | Example3 |
------------------------------------

As you can see, changes.related_id is a foreign key to data.id. changes can have multiple rows of same name and related_id and different comments.

After running this query, I realized that multiple rows are returned where for example, Hello can appear 2 times.

SELECT DISTINCT data.name, changes.comments FROM data LEFT JOIN changes ON data.id = changes.related_id

Result

--------------------
|name   | comments |
|-------------------
|Hello  | Example2 |
|Hello  | Example3 |
--------------------

How do I go about making sure that only 1 row is returned? I went about SO to look for answers and many stated using DISTINCT, yet it's not working in my case.

Thanks in advance.

    • If you only need the name, then only select the name: SELECT DISTINCT data.name FROM data ... But then you actually don't even need a join
      • 1
    • if user have more than one comment, then you will get duplicate name but not record. if you want unique name, then don't select any field from left table and use distinct
      • 2
    • @juergend But I would like to have any associated data from changes table to return with the name. Just that if there are more than 1 row, return the one with the last inserted id. In my case, "Hello" will return ONLY id of 3 and commentsof "Example3"

If you want only one record for every name in your data table and it can be any record, then you can group by the unique column (name) and use an aggregate function on the other column like max()

SELECT data.name, max(changes.comments)
FROM data 
LEFT JOIN changes ON data.id = changes.related_id
GROUP BY data.name
  • 15
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions