mysql - 所有 MySQL 输出行都是重复的

Question

由于某种原因，我的 SELECT 查询返回每行的重复项。以下是我的表格的简要分割:

产品: products_id | master_categories_id | 主分类ID产品型号 |产品状态 |产品数量
类别:categories_id |父 ID
categories_descriptions:categories_id |类别_名称

这个查询工作没有问题...

***QUERY***
SELECT
  categories_name AS category,
  products_model AS model,
  products_status AS status,
  products_quantity AS qty
FROM products p
LEFT JOIN categories c
ON c.categories_id = p.master_categories_id
LEFT JOIN categories_description cd
USING (categories_id)
WHERE p.products_quantity > 0
AND p.products_status = FALSE;


***OUTPUT***
+-------------+------------------------+--------+-----+
| category    | model                  | status | qty |
+-------------+------------------------+--------+-----+
| Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Batteries   | BATT-LHP203            |      0 |   1 |
| Batteries   | BATT-LIB201            |      0 |   1 |
| Refurbished | UC*DELL-OPT-760-DC-IBC |      0 |   9 |
| Desktop     | HDD-320-SATA-3.5-PULL  |      0 |  12 |
| Refurbished | UL*ASUS-S56CM-SH51     |      0 |   1 |
+-------------+------------------------+--------+-----+

但是，类别表包含一个parent_id，它指示您在上面看到的类别的父类别(例如，电缆 > 网络)。这是我尝试包含所述主类别的名称:

***QUERY***
SELECT
  parent_name AS parent,
  categories_name AS category,
  products_model AS model,
  products_status AS status,
  products_quantity AS qty
FROM products p
LEFT JOIN categories c
ON c.categories_id = p.master_categories_id
LEFT JOIN categories_description cd
USING (categories_id)
LEFT JOIN (
  SELECT
    parent_id,
    categories_name AS parent_name
  FROM categories c
  LEFT JOIN categories_description cd
  ON cd.categories_id = c.parent_id
) par
USING (parent_id)
WHERE p.products_quantity > 0
AND p.products_status = FALSE;

***OUTPUT***
+--------------+-------------+------------------------+--------+-----+
| parent       | category    | model                  | status | qty |
+--------------+-------------+------------------------+--------+-----+
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-10-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Cables       | Network     | CP-C-03-CAT6X0         |      0 |   5 |
| Laptop Parts | Batteries   | BATT-LHP203            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LHP203            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LHP203            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LHP203            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LIB201            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LIB201            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LIB201            |      0 |   1 |
| Laptop Parts | Batteries   | BATT-LIB201            |      0 |   1 |
| Desktop PCs  | Refurbished | UC*DELL-OPT-760-DC-IBC |      0 |   9 |
| Desktop PCs  | Refurbished | UC*DELL-OPT-760-DC-IBC |      0 |   9 |
| Hard Drives  | Desktop     | HDD-320-SATA-3.5-PULL  |      0 |  12 |
| Hard Drives  | Desktop     | HDD-320-SATA-3.5-PULL  |      0 |  12 |
| Hard Drives  | Desktop     | HDD-320-SATA-3.5-PULL  |      0 |  12 |
| Hard Drives  | Desktop     | HDD-320-SATA-3.5-PULL  |      0 |  12 |
| Laptops      | Refurbished | UL*ASUS-S56CM-SH51     |      0 |   1 |
| Laptops      | Refurbished | UL*ASUS-S56CM-SH51     |      0 |   1 |
+--------------+-------------+------------------------+--------+-----+

如您所见，每一行都重复多次。我做错了什么？

Answer 1

使用select unique是一个坏习惯。有时这是必要的，但在这种情况下不是。当修复查询足够简单时，为什么还要进行删除重复项的额外工作？

from 子句应如下所示:

FROM products p left join
     categories c
     ON c.categories_id = p.master_categories_id left join
     categories_description cd
     USING (categories_id) left join
     categories_description cdp
     on cdp.categories_id = c.parent_id

额外的子查询不仅会带来额外的处理开销，还会带来重复和不必要的查询复杂性。