mysql - 使一个查询连接多个表

Question

我有 3 个表:

1. 营养表是静态表，包含所有营养 nutrition_id 和 nutrition_name
2. 包含产品信息的产品表，列为 product_id、product_name、brand
3. product_nutritions 这是将产品与其营养成分及其值(value)联系起来的中介表。结构为product_id、nutrition_id、nutrition_value。每个产品可以有 1 行或更多行，具体取决于它所含的营养成分的数量。

这是一个真实的测试例子

---

营养表

nutrition_id |nutrition_name
           1 | caloreis    
           2 | fat    
           3 | sugar    
           4 | salt

---

产品表

product_id| product_name           | brand   
        1 | Nutella Hazelnut Cocoa | Nutella    
        2 | Nutella Jar            | Nutella

---

产品_营养表

product_id | nutrition_id | nutrition_value
         1 |            1 |             200
         1 |            2 |              15
         1 |            3 |               2
         1 |            4 |              11
         2 |            1 |             200
         2 |            2 |              15
         2 |            3 |              12
         2 |            4 |              11

但我需要创建一个查询来返回产品名称以及属于特定品牌的营养成分，其中糖分值小于 5。

这是我试过的查询，但是

 SELECT * 
 FROM products pi
 INNER JOIN product_nutrition_facts pn ON pi.product_id = pn.product_id
 WHERE pn.nutrition_id
 IN (
 '2,4,1'
 )
 OR (
  pn.nutrition_value <=5
  AND pn.nutrition_id =3
 )
 HAVING p.brand ='Nutella'
 ORDER BY `pn`.`nutrition_id` DESC 
 LIMIT 0 , 10

代码会返回营养成分标签中甚至不含糖的产品!

有人可以帮忙吗？

Answer 1

试试这个:

SELECT 
    p.brand,
    p.product_name,
    n.nutrition_name,
    pn.nutrition_value
FROM
    product_nutritions pn,
    Products p,
    Nutritions n
WHERE
    pn.nutrition_id = n.nutrition_id
        AND pn.product_id = p.product_id
        AND p.brand = 'Nutella'
        AND n.nutrition_name = 'sugar'
        AND pn.nutrition_value <= 5;

编辑:试试这个:
SELECT
p.brand,
p.product_name,
n.nutrition_name,
pn.nutrition_value
FROM
product_nutritions pn,
Products p,
Nutritions n
WHERE
pn.nutrition_id = n.nutrition_id
AND pn.product_id = p.product_id
AND p.id IN (
SELECT
p.id
FROM
product_nutritions pn,
Products p,
Nutritions n
WHERE
pn.nutrition_id = n.nutrition_id
AND pn.product_id = p.product_id
AND p.brand = 'Nutella'
AND n.nutrition_name = 'sugar'
AND pn.nutrition_value <= 5
);

对于多种营养素，你可以这样做:
SELECT
p.brand,
p.product_name,
n.nutrition_name,
pn.nutrition_value
FROM
product_nutritions pn,
Products p,
Nutritions n
WHERE
pn.nutrition_id = n.nutrition_id
AND pn.product_id = p.product_id
AND p.id IN (
SELECT
p.id
FROM
product_nutritions pn,
Products p,
Nutritions n
WHERE
pn.nutrition_id = n.nutrition_id
AND pn.product_id = p.product_id
AND
(
(
p.brand = 'Nutella'
AND n.nutrition_name = 'sugar'
AND pn.nutrition_value <= 5
) OR (
p.brand = 'Nutella'
AND n.nutrition_name = 'whatever'
AND pn.nutrition_value <= whatevernumber
)
);

`