mysql 查询多对多关系

Question

我有三个表，一个是产品，第二个是applyedFeaturesValue，第三个是applyedFeatures

在Product表中applyFeaturesValue有ManyToMany关系。

ProductID appliedFeaturesValueId
 1          1
 1          2
 2          1
 3          2

并且在applyFeaturesValue表中与applyFeatures有ManyToOne关系。

appliedFeaturesValue    appliedFeaturesId
    1                          70
    2                          88

我想要获取产品 ID 1，其中 AppliedFeaturesValue Id 1 和 2 都可以代表 AppliedFeaturesId 70 和 88。

如果我使用 IN 子句，则不会返回 AppliedFeaturesValue.id = 1 AND AppliedFeaturesValue.id = 2 的确切产品数据。

如果我使用 AND 子句，则找不到结果。

这是我使用 AND 子句的原始查询，但未找到结果:-

SELECT p0_ FROM Product p0_ INNER JOIN product_applied_features_value p2_ ON p0_.id = p2_.product_id INNER JOIN applied_features_value a1_ ON a1_.id = p2_.applied_features_value_id WHERE p0_.status = 1 AND a1_.applied_features_id = 70 AND a1_.applied_features_id = 88 ORDER BY p0_.id DESC

这是我使用 IN 子句的查询:-

SELECT p0_ FROM Product p0_ INNER JOIN product_applied_features_value p2_ ON p0_.id = p2_.product_id INNER JOIN applied_features_value a1_ ON a1_.id = p2_.applied_features_value_id WHERE p0_.status = 1 AND a1_.applied_features_id IN (70,88) ORDER BY p0_.id DESC

如何获得完全匹配的记录？

Answer 1

尝试下面的查询，它可以帮助您。

SELECT product.* FROM Product product 
INNER JOIN product_applied_features_value prodAppFeatures ON product.appliedFeaturesValueId = prodAppFeatures.id
INNER JOIN applied_features_value appFeatures ON appFeatures.id = prodAppFeatures.applied_features_value_id 
WHERE product.status = 1 AND 
    appFeatures.applied_features_id = 70 AND a1_.applied_features_id = 88 ORDER BY product.id DESC