CODEWITHSUNDEEP
phpjoin
woutr_be
woutr_be

Reputation: 9722

DISTINCT with an inner join

I'm currently working on a website for a photographer, every photo is tagged with multiple keywords which are extracted when uploading.

My database looks like this (simplified)

TAGS
-------------------
|     |           |
|     |           |
| ID  |   TAG     |
|     |           |
|     |           |
-------------------

IMAGES
-------------------
|     |           |
|     |           |
| ID  |   URL     |
|     |           |
|     |           |
-------------------

TAGS_IMAGES
-------------------
|     |           |
| TAG |   IMAGE   |
| ID  |    ID     |
|     |           |
|     |           |
-------------------

So all tags are stored in a seperated table to prevent duplicates, the same goes for the images and then the tags and images are linked together in another table.

When searching for a tag, I execute this SQL to find all images based on the given tag

SELECT DISTINCT SQL_CALC_FOUND_ROWS tags.tag, tags_image.imageID, images.src FROM tags INNER JOIN tags_image ON tags.tagID = tags_image.tagID INNER JOIN images ON tags_image.imageID = images.id WHERE tag LIKE ? ORDER BY id DESC LIMIT ?, ?

But the problem is that I'm still getting multiple duplicates because the DISTINCT only seems to work on the tag.id.

As you can see here: http://pastebin.com/MWt5B0Aq, based on the tag "water", some images have keywords like "water", "watervogel", "waterloop".

Is there a way to get the DISTINCT to work on the images.id?

I tried this, but that didn't help

SELECT DISTINCT images.id SQL_CALC_FOUND_ROWS tags.tag, tags_image.imageID, images.src FROM tags INNER JOIN tags_image ON tags.tagID = tags_image.tagID INNER JOIN images ON tags_image.imageID = images.id WHERE tag LIKE ? ORDER BY id DESC LIMIT ?, ?

Upvotes: 1

Views: 162

Answers (1)

Kris
Kris

Reputation: 6112

Would doing a group by help?

SELECT DISTINCT SQL_CALC_FOUND_ROWS tags.tag, tags_image.imageID, images.src FROM tags INNER JOIN tags_image ON tags.tagID = tags_image.tagID INNER JOIN images ON tags_image.imageID = images.id WHERE tag LIKE ? GROUP BY images.id ORDER BY id DESC LIMIT ?, ?

Upvotes: 1

Related Questions