SQL 根据第二张表的属性过滤一张表中的产品 [英] SQL Filter products from one table basing on attributes from second one

问题描述

所以我有产品的桌子

id | product_name

 1 | Product 1
 2 | Product 2
 3 | Product 3

... 带有属性的表格:

... table with atributtes:

id | attribute

 1 | big
 2 | orange
 3 | expensive

以及包含产品及其属性的表格

and table with products and their attributes

id  | product_id | attribute_id

 1  |     1      |  1
 2  |     1      |  2
 3  |     2      |  3
 4  |     3      |  2

我想要的是过滤大的橙色产品..在这种情况下:产品 1

and what I want is to filter big, orange products.. in this case: Product 1

类似于:

SELECT product_name 
  FROM products as a 
  JOIN products_attributes as b ON a.id=b.product_id 
 WHERE b.attribute_id = 1 OR b.attribute_id=2

不会工作，因为它也会返回产品 3..

will not work as it returns Product 3 as well..

这当然也行不通:

SELECT product_name 
  FROM products as a 
  JOIN products_attributes as b ON a.id=b.product_id
 WHERE b.attribute_id = 1 AND b.attribute_id=2

请帮忙:)

推荐答案

您需要将属性表添加到您的 SELECT 语句和

You need to add atributtes table into your SELECT statement and

按attribute IN ('big','orange')过滤

GROUPing 和 HAVING 子句应该同时满足两个条件

GROUPing with HAVING clause should be added to satisfy the both conditions at the same time

SELECT p.product_name
  FROM products as p
  JOIN products_attributes as pa
    ON p.id = pa.product_id
  JOIN attributes a
    ON a.id = pa.attribute_id  
 WHERE a.attribute IN ( 'big','orange' )
 GROUP BY p.product_name
HAVING COUNT(DISTINCT a.attribute) = 2

使用 IN 而不是 OR 操作符使用起来更直接.

using IN rather than OR operator is more straightforward to use .

演示

这篇关于SQL 根据第二张表的属性过滤一张表中的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！