具有多个条件和多个表的MySQL Query

I have three tables products, properties and product_properties. The subset of table structures and values are given below:

我有三个表产品,属性和product_properties。表结构和值的子集如下:

products
---------------------------------------------
|  id  |  name  |   description   |  price  |
---------------------------------------------
|   1  | Camera |  Color Camera   |   100   |
|   2  | Lens 1 |  Camera Lens 1  |   20    |
|   3  | Lens 2 |  Camera Lens 2  |   30    |
---------------------------------------------

properties
------------------------------------------
|  id |  name          |  display        |
------------------------------------------
|  1  |  lens_mount    |   Lens Mount    |
|  2  |  image_circle  |   Image Circle  |
|  3  |  focal_length  |   Focal Length  |
|  4  |  lens_family   |   Lens Family   |
------------------------------------------

product_properties
------------------------------------------------
|  id  | value    | product_id |  property_id  |
------------------------------------------------
|  1   | F-Mount  | 2          |  1            |
|  2   | C-Mount  | 3          |  1            |
|  3   | 42.01 mm | 2          |  2            |
|  4   | 13.00 mm | 3          |  2            |
|  5   | 10.00    | 2          |  3            |
|  6   | 12.00    | 3          |  3            |
|  7   | Standard | 1          |  4            |
|  8   | Standard | 2          |  4            |
|  9   | Standard | 3          |  4            |
------------------------------------------------

Here is my output condition:

这是我的输出条件:

Find all the Lenses for Camera 1 (match camera and lens by lens_family, which is 'Standard' here) which have lens_mount = 'F-Mount' and image_circle >= 40 mm and focal_length > 5

找到相机1的所有镜头(通过lens_family匹配相机和镜头,这里是'标准'),其中lens_mount ='F-Mount',image_circle> = 40 mm,focal_length> 5

I tried following query for this:

我尝试了以下查询:

SELECT * FROM products 
  INNER JOIN product_properties ON products.id = product_properties.product_id
  INNER JOIN properties ON properties.id = product_properties.property_id 
  WHERE (product_properties.value = 'Standard') 
    AND (properties.name = 'lens_mount' AND product_properties.value = 'F-Mount') 
    AND (properties.name = 'image_circle' AND product_properties.value >= ABS('40 mm')) 
    AND (properties.name = 'focal_length' AND product_properties.value >= 5)

However this query only gives correct result if there is only one condition. With all the conditions it doesn't give any value. I tried with OR in place of AND in the where condition but that also didn't help to get the correct output.

但是,如果只有一个条件,则此查询仅提供正确的结果。在所有条件下,它没有给出任何价值。我尝试用OR代替AND在where条件下,但也没有帮助获得正确的输出。

Can anyone sortout this please. Thanks in advance.

任何人都可以解决这个问题。提前致谢。

3 个解决方案

#1

You want to do the logic in the having clause rather than in the where clause:

您想要在having子句中而不是在where子句中执行逻辑:

SELECT products.id
FROM products 
  INNER JOIN product_properties ON products.id = product_properties.product_id
  INNER JOIN properties ON properties.id = product_properties.property_id 
group by products.id
having sum(properties.name = 'lens_family' AND product_properties.value = 'Standard') > 0 and
       sum(properties.name = 'lens_mount' AND product_properties.value = 'F-Mount') > 0 and
       sum(properties.name = 'image_circle' AND product_properties.value >= ABS('40 mm')) > 0 and
       sum(properties.name = 'focal_length' AND product_properties.value >= 5) > 0;

You are looking for a set of properties on a single product. No single row can match all the conditions -- they conflict with each other. Instead, use the group by clause to bring the rows together for a given product. Then count the number of rows that match each condition.

您正在寻找单个产品的一组属性。没有一行可以匹配所有条件 - 它们相互冲突。相反,使用group by子句将给定产品的行组合在一起。然后计算匹配每个条件的行数。

Each clause in the having corresponds to one of your original clauses in the where statement, enclosed by sum(). This counts the number of rows that match. The conditions ensure that there is at least one row for each property.

has中的每个子句对应于where语句中的一个原始子句,由sum()括起来。这会计算匹配的行数。条件确保每个属性至少有一行。

#2

Ah, the dreaded EVA schema. The first think you should do is create a view to normailze the data as per the following SQL.

啊,可怕的EVA架构。您应该首先考虑创建一个视图来按照以下SQL对数据进行规范化。

You can then use two copies of that view (one for the camera, one for the lens) and join them together to get what you need.

然后,您可以使用该视图的两个副本(一个用于相机,一个用于镜头)并将它们连接在一起以获得所需内容。

SELECT  p.id
        ,p.name
        ,pp1.value as Lens_Mount
        ,pp2.value as Image_Circle
        ,pp3.value as Focal_Length
        ,pp4.value as Lens_Family
from    products p
left outer join
        product_properties pp1
on      pp1.product_id = p.id
and     pp1.property_id = 1 --lens Mount
left outer join
        product_properties pp2
on      pp2.product_id = p.id
and     pp2.property_id = 2 --Image Circle
left outer join
        product_properties pp3
on      pp3.product_id = p.id
and     pp3.property_id = 3 --Focal Length
left outer join
        product_properties pp4
on      pp4.product_id = p.id
and     pp4.property_id = 3 --Lens Family        
where   p.name like 'Lens%'

#3

You should OR every condition and count how many of them are satisfied for each product_id. So you GROUP BY product_id and filter results using HAVING clause.

你应该OR每个条件并计算每个product_id满足多少条件。所以你使用HAVING子句对GROUP BY product_id和过滤结果进行了分析。

SELECT product_properties.product_id, count(*) as conditions_satisfied FROM products 
  INNER JOIN product_properties ON products.id = product_properties.product_id
  INNER JOIN properties ON properties.id = product_properties.property_id 
WHERE product_properties.value = 'Standard'
 OR properties.name = 'lens_mount' AND product_properties.value = 'F-Mount'
 OR properties.name = 'image_circle' AND product_properties.value >= ABS('40 mm')
 OR properties.name = 'focal_length' AND product_properties.value >= 5
GROUP BY product_properties.product_id
HAVING COUNT(*) = 4

Note it may be difficult to compare property values that are strings if you really need a number comparison.

请注意,如果您确实需要进行数字比较,则可能难以比较字符串的属性值。

#1