CODEWITHSUNDEEP
mysqlsql
user1535190
user1535190

Reputation: 47

SQL Filtering Through Multiple Tags

I have three tables, images, tags and links(the table below) 

   ImageNO | TagNO
     1     | Sport
     2     | Cars
     3     | Sport
     4     | Sport
     3     | Cars
     1     | Music
     2     | Sport

(Obviously TagNO would be a number in the actual table)

What I want to do is allow filtering such that users can select any number of tags and images will be filtered to only the images with all of the tags chosen. For example if a user selects "sport", "imageNO" 1, 3 , 4 and 2 would be displayed. If the user also selects "cars" the query is refined and only "imageNO" 3 and 2 will be displayed.

I have tried a number of things so far from examples across the web, this is the closest i have come to... 

SELECT *
 FROM  images 
  LEFT JOIN links 
   ON INO = links.INO
  LEFT JOIN tags 
   ON tags.TagNO = links. TagNO
  WHERE links. TagNO
  IN ($tags)
  GROUP BY INO
  HAVING COUNT( INO ) >1

($tags) is an array of tag numbers.

The problem with this is an image with only one tag will never be displayed as the count number is = 1 not > 1. Any help would be much appreciated, thanks in advance.

Upvotes: 2

Views: 1829

Answers (2)

Nick F
Nick F

Reputation: 10142

I think this question addresses the same problem, and the accepted answer's approach would probably work for you too. Something along the lines of:

SELECT * 
FROM images i
JOIN links l ON l.INO = i.INO
JOIN tags t ON t.TagNO = l.TagNO
WHERE l.TagNO IN ('sports','cars')
GROUP BY i.INO
HAVING COUNT(DISTINCT t.TagNO) = 2

...the important bit being the COUNT(DISTINCT t.TagNO) = [number of tags]

Upvotes: 3

kmkaplan
kmkaplan

Reputation: 18970

You have to join multiple times on the same table. Use aliases to this end:

SELECT * FROM images
  LEFT JOIN links AS l1 ON l1.INO = images.INO
  LEFT JOIN tags AS t1 ON t1.TagNO = l1.TagNO
  LEFT JOIN links AS l2 ON l2.INO = images.INO
  LEFT JOIN tags AS t2 ON t2.TagNO = l2.TagNO
  [...]
  WHERE t1.tagNO = $tag1
    AND t2.tagNO = $tag2
  [...]

Upvotes: 1

Related Questions