MySQL搜索产品及其属性 [英] MySQL search products with their attributes

问题描述

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

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';

所以我需要搜索一个或多个产品具有128MB或256MB RAM和32GB HDD

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

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

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联接:

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) )

演示

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

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

这篇关于MySQL搜索产品及其属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！