SQL Many To Many Select With Link Table

Question

I am trying to make my keyword search as efficient as possible using the following 3 tables :

tblImageFiles [ID, ImageURL]

tblTags [ID,Tag]

tblxImagesTags [ID, ImageID, TagID] (this is a linktable joining the above in a many-to-many relationship)

Can anyone help me out with a stored procedure to return ALL images which match ALL search tags entered based on this schema?

Thanks

Similar question (with lots of ways to do this): [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ᵀᴹ, May 18 '12 at 11:48

aF. · Accepted Answer · 2012-05-18T12:23:33.690

2

The number in the count(distinct aux.TagID) must be equal to the number of tags in where t.Tag in (tag1, tag2, tag3).

select img.* from tblImageFiles img
inner join (
   select it.ImageID from tblTags t
   inner join tblxImagesTags it on it.TagID = t.ID
   where t.Tag in (tag1, tag2, tag3)
   group by it.ImageID
   having count(distinct it.TagID) = 3
) aux on aux.ImageID = img.ID

edited May 18 '12 at 12:23

answered May 18 '12 at 09:33

aF.

64,980
43
135
198

thanks but I am getting the following message when I run the query : Error in SELECT clause: expression near 'IF' Any ideas? – Mike May 18 '12 at 09:58
@Mike yes I corrected. I was using if as an alias but it's a reserved key. And I alter the code to put it like you wanted. – aF. May 18 '12 at 10:00
Ah, this works but same issue as with Matts suggestion, it returns images matching any of the tags rather than filtering, any suggestions on how I can achieve this? – Mike May 18 '12 at 10:06
@Mike have you used the `group by` query? Try it again. If you put more tags you need to replace the `3` by the total number of tags. – aF. May 18 '12 at 10:08
sorry about that I hadn't refreshed the page until after I posted my reply :/ if I paste your query I get this error : "The multi-part identifier "aux.ImageID" could not be bound.. not sure what is causing it though..! – Mike May 18 '12 at 10:40
@Mike in Sybase in worked well :P I've replace the outer aux by aux2, now it should work for you. – aF. May 18 '12 at 10:54
It's wrong. There is no `aux` alias or table, anywhere in the query. – ypercubeᵀᴹ May 18 '12 at 11:46

Matt Gibson · Answer 2 · 2012-05-18T11:33:07.980

        SELECT tblImageFiles.ImageURL
          FROM tblImageFiles
  WHERE EXISTS (SELECT 1
                  FROM tblxImagesTags
            INNER JOIN tblTags
                    ON tblTags.ID = tblxImagesTags.TagID
                 WHERE tblxImagesTags.ImgageID = tblImageFiles.ID
                   AND tblTags.Tag = <searchtag1>)
    AND EXISTS (SELECT 1
                  FROM tblxImagesTags
            INNER JOIN tblTags
                    ON tblTags.ID = tblxImagesTags.TagID
                 WHERE tblxImagesTags.ImgageID = tblImageFiles.ID
                   AND tblTags.Tag = <searchtag2>)
                // etc...

The derived table could be separated out, but the syntax for that depends on what DB you are using. You also need to run a loop to make sure you can cope with a variable number of search terms, but this is the basic idea.

thanks, this seems close but returns images matching any of the tags, I need it to match all of them if possible so, for example, searching 'Door' should produce more results than 'Red, Door' as it should filter — Mike, May 18 '12 at 09:55

SQL Many To Many Select With Link Table

2 Answers2