我在从一个表中获取所有记录时遇到了问题，即使没有关系。我的第一个表（warehouse）有一个仓库类型，它是来自另一个表的外键。还有一个表包含了可以存在于不同仓库中的所有类型的产品，它们是根据仓库的类型定义的（我的仓库类型与产品类型的关系表），我的目标是获取所有的产品类型，指示基于仓库的类型将哪些分配给仓库以及不将哪些分配给仓库。
我尝试了不同的方式查询：使用UNION，子查询，但我没有实现我的目标。我包括最接近预期结果的查询：
SQL Fiddle

SELECT DISTINCT w.id, w.name, wht.type AS whouse_type, APT.product_type_id, APT.product_type,
CASE
    WHEN w.type_id = APT.warehouse_type_id
        THEN "YES"
    WHEN w.type_id <> APT.warehouse_type_id
        THEN "NO"
    WHEN APT.warehouse_type_id IS NULL
        THEN "NO"
    ELSE NULL
END AS assigned
FROM warehouse w
LEFT JOIN warehouse_types wht ON w.type_id = wht.id
CROSS JOIN (
    SELECT DISTINCT pt.id AS product_type_id, pt.product_type, rel.warehouse_type_id
    FROM product_types pt 
    LEFT JOIN products_type_by_wh_types rel ON pt.id = rel.product_type_id
  ) AS APT -- all products types 
where w.id = 1
ORDER BY w.name, APT.product_type_id, type_of_warehouse;

这给了我：

+----+-------------+-------+--------------+----------+
| id | whouse_type | pt_id | product_type | assigned |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   1   | Industry     | YES      |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   1   | Industry     | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   2   | Transport    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   3   | Chemicals    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   4   | Food and B   | YES      |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   4   | Food and B   | NO       |
+----+-------------+-------+--------------+----------+

预期结果应为：

+----+-------------+-------+--------------+----------+
| id | whouse_type | pt_id | product_type | assigned |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   1   | Industry     | YES      |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   2   | Transport    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   3   | Chemicals    | NO       |
+----+-------------+-------+--------------+----------+
| 1  | NORTH       |   4   | Food and B   | YES      |
+----+-------------+-------+--------------+----------+