MySQL LEFT JOIN display duplicate rows

Question

I'm having an issue with an MySQL left join it returns duplicate rows for the same name.

            $result = mysql_query("SELECT
            w.id AS 'WheelID',
            w.name AS 'Name',
            i.id AS 'ImageID',
            i.image_url AS 'Image'
            FROM `rims` AS w
            LEFT JOIN `rims_images` AS i ON
            (w.`id` = i.`mid`)
            WHERE w.`id` =  '".$model."'

           ");
              while($row = mysql_fetch_array($result))
            {

                   echo $row['Name'];
                echo $row['Image'];

            }

Thank you any help will gladly be appreciated.

If there are multiple matches, `LEFT JOIN` will return all of them, just like `INNER JOIN` does. Why do you expect anything different? — Barmar, Apr 25 '14 at 19:22
well thats normal since its one to many meaning rims is attached to multiple rims_images so in a single join its expected this way. One thing could be done is to group data from rims_images — Abhik Chakraborty, Apr 25 '14 at 19:23
I am going for displaying the Name and just the image below. — st0rm, Apr 25 '14 at 19:23
If there are 3 images of the same rim, which one should it display? — Barmar, Apr 25 '14 at 19:24
Your question is misleading. You're not getting duplicate rows in the result. Each result has a different `ImageID` and `Image`. — Barmar, Apr 25 '14 at 19:25
@Barmar I am trying to display the name of the rim and show all the different images for that particular rim. — st0rm, Apr 25 '14 at 19:26
Your left join displays duplicates, because your left table contains duplicates of the columns you are querying. if you only want one resultrow per name, you can add a `GROUP BY w.name`. You should however be aware that you have several images matching one name, so you should either rethink your structure, or decide which one to pick. Otherwise you will get a "randomly" chosen image matching the name. — Max, Apr 25 '14 at 19:33

score 0 · Accepted Answer · answered Apr 25 '14 at 19:28

0

Only display the Name once in the loop:

$first = true;
while ($row = mysql_fetch_assoc($result)) {
    if ($first) {
        echo $row['Name'];
        $first = false;
    }
    echo $row['Image'];
}

answered Apr 25 '14 at 19:28

Barmar

741,623
53
500
612

this only works for the first name. if you want it to work for several distinct names, you need to store the last name read and check against the current row. – Max Apr 25 '14 at 19:36
The query only returns one rim, because it has `WHERE w.id = $model`. The answer would be different if he were listing multiple rims, I've written that type of loop many times in other answers. – Barmar Apr 25 '14 at 19:38

score -1 · Answer 2 · answered Apr 25 '14 at 19:23

-1

Add DISTINCT to query after your SELECT.

That will filter out your duplicates from results.

answered Apr 25 '14 at 19:23

MRawrawk

83
2
4

He's not getting duplicates. He's getting 3 different images. – Barmar Apr 25 '14 at 19:24
But there are no duplicates. He has three rows which happen to have the same value for the first column, but different values for the second column. `DISTINCT` will not help him here - he needs to figure out what he's trying to get from the database. – Kryten Apr 25 '14 at 19:25
no distinct does not solve the issue is one-to-many issue , in a single join query it will join both tables and will return all matching data. – Abhik Chakraborty Apr 25 '14 at 19:25
in general, distinct should be used with caution because distinct records are not always distinct records – Marshall Tigerus Apr 25 '14 at 19:26

MySQL LEFT JOIN display duplicate rows

2 Answers2

Linked