mysql - 从一个表中选择一个 "item"并从另一个表中选择其特定的 "properties"

Question

我有一个项目列表，我正在尝试检索它们的所有属性。某些属性对于所有项目都是通用的，并在 Product 表中定义，其他属性特定于每种类型的项目，并插入到每种类型的项目的专用表中。

表格如下所示:

mysql> select * from Product;
+----+--------+-------+----------+------+
| id | name   | price | quantity | type |
+----+--------+-------+----------+------+
|  1 | Book1  |    10 |       20 |    1 |
|  2 | Book2  |    20 |       30 |    1 |
|  3 | Books3 |    18 |       20 |    1 |
|  4 | Car1   |   150 |       10 |    2 |
|  5 | Car2   |   180 |       30 |    2 |
|  6 | Car3   |   170 |       10 |    2 |
+----+--------+-------+----------+------+

mysql> select * from Product_Book;
+----+------------+---------+--------+
| id | product_id | author  | title  |
+----+------------+---------+--------+
|  1 |          1 | Author1 | Title1 |
|  2 |          2 | Author2 | Title2 |
|  3 |          3 | Author3 | Title3 |
+----+------------+---------+--------+

mysql> select * from Product_Car;
+----+------------+-------+-------+-------+
| id | product_id | doors | color | make  |
+----+------------+-------+-------+-------+
|  1 |          4 |     4 | red   | make1 |
|  2 |          5 |     4 | blue  | make2 |
|  3 |          6 |     2 | black | make3 |
+----+------------+-------+-------+-------+

如何根据 Product.type 选择所有项目及其所有属性？

我尝试过在 SELECT 中使用 CASE，但效率不高:

SELECT DISTINCT
    Product.id,
    (CASE Product.type
        WHEN 1 THEN Product_Book.author
    END) as book_author,
    (CASE Product.type
        WHEN 2 THEN Product_Car.color
    END) as car_color
FROM
    Product,
    Product_Car,
    Product_Book
WHERE
    Product.id = Product_Book.product_id or
    Product.id = Product_Car.product_id
ORDER BY Product.id; 

+----+-------------+-----------+
| id | book_author | car_color |
+----+-------------+-----------+
|  1 | Author1     | NULL      |
|  2 | Author2     | NULL      |
|  3 | Author3     | NULL      |
|  4 | NULL        | red       |
|  5 | NULL        | blue      |
|  6 | NULL        | black     |
+----+-------------+-----------+

我应该在 FROM 子句中使用 CASE 吗？这种情况有通用模式吗？是否可以将特定项目属性分组到一个额外列中，其中包含特定属性，例如？

+----+--------+-------+----------+------+-------+
| id | name   | price | quantity | type | extra |
+----+--------+-------+----------+------+-------+

谢谢。

Answer 1

您好，正如我在评论中建议您必须使用 join 子句。

select distinct * from Product 
left join Product_Book on Product.id=Product_Book.product_id
left join Product_Car on Product.id=Product_Car.product_id

尝试一下并给我反馈。