0

Okay, so i have three tables, images(ID, name,..), tags(ID, name, ..) and one with the connections image_tags(imageID, tagID).

Here's my problem, I'm trying to write a query that gets all the images that has 2+ specific tag IDs.

Sounds simple enough or so i thought. I know I can do it with getting all the images that has one tag and then check all of them if they have the other ones but the cost to performance is a bit too high that way.

Would appreciate any input on the matter..

Florent
  • 12,310
  • 10
  • 49
  • 58
ember
  • 3
  • 1
  • Well i use a join for when i want all the images with just one tag but that doesn't work for multiple.. – ember Aug 31 '12 at 11:33
  • 1
    Post your sql code, post the results, and tell us whats wrong with the results you got. – BugFinder Aug 31 '12 at 11:35
  • See this question: [**How to filter SQL results in a has-many-through relation**](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation) – ypercubeᵀᴹ Aug 31 '12 at 13:13

3 Answers3

0

try this

SELECT images.ID, COUNT(DISTINCT tags.ID) as counter FROM images
LEFT JOIN image_tags ON (images.ID=image_tags.imageID)
LEFT JOIN tags ON (image_tags.tagID=tags.ID)
GROUP BY images.ID HAVING COUNT (tags.ID) >=2
JaMaBing
  • 1,051
  • 14
  • 32
  • Sorry i seem to not have been clear enough in my question, what i'm trying to get is all the images with specific tagIDs like tagID = 1020 AND tagID = 1016 not just a list with all the images that has more than two tags. – ember Aug 31 '12 at 11:53
0

I think the query you want is this:

SELECT images.ID, COUNT(DISTINCT tags.ID) as counter
FROM image_tags it
group by it.imageID
having max(case when it.tagID = 1020 then 1 else 0 end) = 1 and
       max(case when it.tagID = 1016 then 1 else 0 end) = 1

The group by brings together all the tags for a given image. The having clause makes sure that it has the two tags that you want. If you want only those two tags, then you can add the clause:

max(case when it.tagID not in (1020, 1016) then 1 else 0 end) = 0
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

Would that work for you?

SELECT * from image_tags i1, image_tags i2
WHERE i1.imageID = i2.imageID
AND i1.tagID != i2.tagID
m4573r
  • 992
  • 7
  • 17
  • This looks like it's working :D Going to be silly large queries when there are a lot of tags but i guess it cant be helped. thanks a lot for the help :) – ember Aug 31 '12 at 11:59
  • Looking at your comment replying @JaMaBing, it seems that you should get rid of the `GROUP BY`... But glad I could help :) – m4573r Aug 31 '12 at 12:04
  • Yea, already got rid of it, does nothing once i add the tagID conditions ;D – ember Aug 31 '12 at 12:07