SQL多对多用链接表选择

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

我正在尝试使用以下3个表格尽可能高效地进行关键字搜索:

tblImageFiles [ID, ImageURL]

tblImageFiles [ID,ImageURL]

tblTags [ID,Tag]

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

tblxImagesTags [ID,ImageID,TagID](这是一个以多对多关系加入上述内容的链接表)

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

任何人都可以帮助我使用存储过程返回所有匹配所有基于此模式输入的搜索标签的图像吗?

Thanks

2 个解决方案

#1

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

计数中的数字(不同的aux.TagID)必须等于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

#2

        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.

派生表可以分离出来,但其语法取决于您使用的DB。您还需要运行循环以确保您可以处理可变数量的搜索项,但这是基本的想法。