CODEWITHSUNDEEP
mysqlsqljoinduplicates
directory
directory

Reputation: 3167

Prevent mysql query from giving multiple rows of same id

At first, it's a bit complicated to create an SQL fiddle with the problem because the data amount is huge. By this I try to drop an screenshot with the result I have.

The problem seems to be small, my query runs perfectly but because different images are linked to different locations in the same city (or cities in range of the distance) I get back two times or more the same image at this time. Because this query is running on 'city' niveau, I just need to get back one image as I need the first one out of the results (supposed to be the closest one to my lan/lon).

The query:

SELECT c.slug
    ,l.location_id
    ,fl.flyer_id
    ,f.salt
    ,f.NAME
    ,f.online
    ,f.cover
    ,fc.cat_id
    ,ca.NAME AS cat_name
    ,lan
    ,lon
    ,(6371 * acos(cos(radians(1.5000759112888)) * cos(radians(lan)) * cos(radians(lon) - radians(2.6118285879120)) + sin(radians(1.5000759112888)) * sin(radians(lan)))) AS distance
FROM cities c
INNER JOIN locations l
    ON c.slug = l.city_slug
INNER JOIN flyers_locations fl
    ON l.location_id = fl.location_id
INNER JOIN flyers_categories fc
    ON fl.flyer_id = fc.flyer_id
INNER JOIN categories ca
    ON fc.cat_id = ca.id
INNER JOIN flyers f
    ON fl.flyer_id = f.id
HAVING distance < 25
    AND l.location_id != ''
    AND fc.cat_id = '41'
    AND f.online = '1'
ORDER BY distance ASC 
LIMIT 100

The result

enter image description here

enter image description here

Many thanks

Upvotes: 0

Views: 593

Answers (1)

user5400659
user5400659

Reputation: 97

If you don´t want an image to appear twice you could try using distinct in front of the column name.

But if you realy only want to fetch the first one why not just set the limit to 1? This way you would only get the first (closest) row.

Upvotes: 1

Related Questions