SQL如何到达并检查另一个查询中的查询元素？(多对多)

Question

用户选择最喜欢的购物中心、品牌和部门

我试图显示的运动，是有效的用户的最爱。

表:活动(有商场和品牌)，MallCampaign，购物中心，品牌，部门，用户，最爱

例如，用户选择了X、Y、Z(Z:不包括阿迪达斯商店)购物中心；阿迪达斯、Levi's、维多利亚秘密品牌；shoesE 211部门。因此，用户应该只看到阿迪达斯的运动，它在X，Y购物中心是有效的。

如何组合这些查询？我应该使用联接还是如果存在？

    SELECT mc.CampaignId, c.CampaignTitle
    FROM MallCampaign mc 
    JOIN Mall m ON m.MallId = mc.MallId
    JOIN Campaign c ON c.CampaignId = mc.CampaignId
    JOIN Brand b ON b.BrandId = c.BrandId


    SELECT FavoriteItemId AS FavoriteMalls
    FROM Favorite
    WHERE UserId = @UserId AND FavoriteItemType=1 

    SELECT FavoriteItemId AS FavoriteBrands
    FROM Favorite
    WHERE UserId = @UserId AND FavoriteItemType=3 

    SELECT FavoriteItemId AS FavoriteSectors
    FROM Favorite
    WHERE UserId = @UserId AND FavoriteItemType=2 

Answer 1

我建议你使用in

SELECT mc.CampaignId, c.CampaignTitle
FROM MallCampaign mc 
where mallid in (SELECT FavoriteItemId AS FavoriteMalls
                 FROM Favorite
                 WHERE UserId = @UserId AND FavoriteItemType=1
                ) and
      brandId in (SELECT FavoriteItemId AS FavoriteBrands
                  FROM Favorite
                  WHERE UserId = @UserId AND FavoriteItemType=3
                 ) and
      sectorid in (SELECT FavoriteItemId AS FavoriteSectors
                   FROM Favorite
                   WHERE UserId = @UserId AND FavoriteItemType=2 
                  )

这说明了查询的目的。而且，如果您有一个关于Favorite(UserId, FavorteItemType, FavoriteItemId)的索引，那么性能应该是相当好的。