MySQL搜索带有属性的产品(MySQL search products with their attributes)

MySQL搜索带有属性的产品(MySQL search products with their attributes)

我有一个MySQL数据库，其中包含带有组/属性的产品/我创建了一个数据样本

http://sqlfiddle.com/#!2/7d8a04/1

create table products ( id int(10), title varchar(50) ); create table attributes ( id int(10), title varchar(50) ); create table filters ( id int(10), attribute_id int(10), title varchar(50) ); create table product_filters ( id int(10), product_id int(10), attribute_id int(10), filter_id int(10) ); #products insert into products select '1', '1stphone'; insert into products select '2', '2ndphone'; #attributes insert into attributes select '1', 'ram'; insert into attributes select '2', 'cpu'; insert into attributes select '3', 'hdd'; #filters insert into filters select '1', '1','128MB'; insert into filters select '2', '1','256MB'; insert into filters select '3', '2','1GHz'; insert into filters select '4', '2','2GHz'; insert into filters select '5', '3','16GB'; insert into filters select '6', '3','32GB'; #product_filters insert into product_filters select '1','1','1','2'; insert into product_filters select '2','1','2','4'; insert into product_filters select '3','1','3','6'; insert into product_filters select '4','2','1','1'; insert into product_filters select '5','2','2','3'; insert into product_filters select '6','2','3','5';

所以我需要搜索该产品（或产品）有128MB或256MB RAM和32GB硬盘

如果你看到这个链接http://sqlfiddle.com/#!2/7d8a04/1你可以看到我的查询，但我不知道它为什么返回0行。

SELECT DISTINCT products.* FROM products JOIN product_filters ON product_filters.product_id=products.id # where_ram_is_128MB_OR_256MB # it works !! where ( product_filters.attribute_id=1 and product_filters.filter_id in (1,2) ) # and_where_hdd_is_32GB # not_works !! AND ( product_filters.attribute_id=3 and product_filters.filter_id in (6) )

I have a MySQL database that contains products with their group/attributes/ i created a sample of data in

http://sqlfiddle.com/#!2/7d8a04/1

create table products ( id int(10), title varchar(50) ); create table attributes ( id int(10), title varchar(50) ); create table filters ( id int(10), attribute_id int(10), title varchar(50) ); create table product_filters ( id int(10), product_id int(10), attribute_id int(10), filter_id int(10) ); #products insert into products select '1', '1stphone'; insert into products select '2', '2ndphone'; #attributes insert into attributes select '1', 'ram'; insert into attributes select '2', 'cpu'; insert into attributes select '3', 'hdd'; #filters insert into filters select '1', '1','128MB'; insert into filters select '2', '1','256MB'; insert into filters select '3', '2','1GHz'; insert into filters select '4', '2','2GHz'; insert into filters select '5', '3','16GB'; insert into filters select '6', '3','32GB'; #product_filters insert into product_filters select '1','1','1','2'; insert into product_filters select '2','1','2','4'; insert into product_filters select '3','1','3','6'; insert into product_filters select '4','2','1','1'; insert into product_filters select '5','2','2','3'; insert into product_filters select '6','2','3','5';

so I need to search that product (or products ) has 128MB OR 256MB RAM and 32GB HDD

If you see this link http://sqlfiddle.com/#!2/7d8a04/1 you can see my query but I dont know why it return 0 row.

SELECT DISTINCT products.* FROM products JOIN product_filters ON product_filters.product_id=products.id # where_ram_is_128MB_OR_256MB # it works !! where ( product_filters.attribute_id=1 and product_filters.filter_id in (1,2) ) # and_where_hdd_is_32GB # not_works !! AND ( product_filters.attribute_id=3 and product_filters.filter_id in (6) )

最满意答案

您需要为每个属性分别加入product_filters ：

SELECT DISTINCT products.* FROM products JOIN product_filters AS f1 ON f1.product_id=products.id JOIN product_filters AS f2 ON f2.product_id=products.id WHERE ( f1.attribute_id=1 and f1.filter_id in (1,2) ) AND ( f2.attribute_id=3 and f2.filter_id in (6) )

DEMO

您的版本试图在product_filters中查找具有两个属性ID的单行，这是不可能的。

You need to join with product_filters separately for each attribute:

SELECT DISTINCT products.* FROM products JOIN product_filters AS f1 ON f1.product_id=products.id JOIN product_filters AS f2 ON f2.product_id=products.id WHERE ( f1.attribute_id=1 and f1.filter_id in (1,2) ) AND ( f2.attribute_id=3 and f2.filter_id in (6) )

DEMO

Your version tried to find a single row in product_filters that has both attribute IDs, which isn't possible.