CODEWITHSUNDEEP
phpmysql
Super Cat
Super Cat

Reputation: 1667

MySQL - JOIN producing "extra" results?

It would be rather difficult to explain this issue without showing you what I've got, so we'll start with that:

https://i.sstatic.net/scpv6.png

https://i.sstatic.net/XZFQR.png

Query:

SELECT goodies.title FROM `goodies` LEFT JOIN `goody_images`
ON goodies.id=goody_images.goodie_id

Raw Result:

candy
candy
hats

The issue:

It produces an extra result per each joined value. Because two of the entires in goody_images have the a goodie_id of 1, it prints the corresponding title from goodies twice, hence why we see the result candy twice. I instead need it to only give my the title once, regardless of the number of matching JOIN results.

Is this possible, or am I better off running two separate queries?

In context of PHP, I'm trying to get two values:

-One with the title,
-And and an array with the matching images.

The programming for all of that is already complete are perfectly operational - The issue simply lies in getting the results properly.

+The above query was abbreviated for simplicity

Upvotes: 0

Views: 50

Answers (2)

user3712320
user3712320

Reputation: 116

It is not showing extra record it showing candy two time because goodie_id 1 has two images but if you want to select title only then you can use 

SELECT goodies.title FROM `goodies` LEFT JOIN `goody_images`
ON goodies.id=goody_images.goodie_id group by goodies.title

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Either use distinct

SELECT distinct goodies.title 
FROM `goodies` 
LEFT JOIN `goody_images` ON goodies.id=goody_images.goodie_id

or group by

SELECT goodies.title 
FROM `goodies` 
LEFT JOIN `goody_images` ON goodies.id=goody_images.goodie_id
GROUP BY goodies.title

Upvotes: 5

Related Questions