Search code examples
sqlsearchstored-proceduresmany-to-manysql-match-all

SQL Many To Many Select With Link Table

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

Solution

  • 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