我有一个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.
更多推荐
发布评论