php - 左连接多个表

Question

我的mysql数据库结构如下

产品表

+----+--------+-------------+-------+
| id | itemid |    title    | price |
+----+--------+-------------+-------+
|  1 |      1 | Dell XPS 10 |  1000 |
|  2 |      1 | Dell XPS 13 |  1100 |
|  3 |      1 | HP 1        |  1100 |
+----+--------+-------------+-------+

项目表

+----+-------------+
| id | description |
+----+-------------+
|  1 | Laptop      |
|  2 | Monitors    |
+----+-------------+

参数表

+----+--------+------------------+
| id | itemid |   description    |
+----+--------+------------------+
|  1 |      1 | Brand            |
|  2 |      1 | Series           |
|  3 |      1 | Color            |
|  4 |      1 | Operating System |
+----+--------+------------------+

规范表

+----+-----------+-------------+-------------+
| id | productid | parameterid | description |
+----+-----------+-------------+-------------+
|  1 |         1 |           1 | Dell        |
|  2 |         1 |           2 | XPS         |
|  3 |         1 |           3 | Red         |
|  4 |         1 |           4 | Windows 10  |
|  5 |         2 |           1 | Dell        |
|  6 |         2 |           2 | XPS         |
|  7 |         2 |           3 | Black       |
|  8 |         2 |           4 | Windows 7   |
|  9 |         3 |           1 | HP          |
| 10 |         3 |           2 | 1           |
| 11 |         3 |           3 | Black       |
| 12 |         3 |           4 | Windows 10  |
+----+-----------+-------------+-------------+

我正在尝试得出以下结果

 +----+-----------+-------------+-------+-----------------+----------------------+--------------------------+
 | id | productid |    title    | price | itemdescription | parameterdescription | specificationdescription |
 +----+-----------+-------------+-------+-----------------+----------------------+--------------------------+
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Brand                | Dell                     |
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Series               | XPS                      |
 +----+-----------+-------------+-------+-----------------+----------------------+--------------------------+

我得到的不是这个

 +----+-----------+-------------+-------+-----------------+----------------------+--------------------------+
 | id | productid |    title    | price | itemdescription | parameterdescription | specificationdescription |
 +----+-----------+-------------+-------+-----------------+----------------------+--------------------------+
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Brand                | Dell                     |
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Brand                | XPS                      |
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Brand                | Red                      |
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Brand                | Windows 10                      |
 +----+-----------+-------------+-------+-----------------+----------------------+--------------------------+

我正在使用以下 mysql 查询

SELECT products.title, products.price, items.description, parameters.description, specifications.description
FROM products
LEFT JOIN items ON products.itemid = items.id
LEFT JOIN parameters ON parameters.itemid = items.id
LEFT JOIN specifications ON specifications.productid = products.id
LIMIT 0 , 30

其次是否可以将所有参数和规范列在同一行中？

 +----+-----------+-------------+-------+-----------------+--------------------------+--------------------------+--------------------------+
 | id | productid |    title    | price | itemdescription | specificationdescription | specificationdescription | specificationdescription |
 +----+-----------+-------------+-------+-----------------+--------------------------+--------------------------+--------------------------+
 |  1 |         1 | Dell XPS 10 |  1000 | Laptop          | Dell                     | XPS                      | Red                      |
 +----+-----------+-------------+-------+-----------------+--------------------------+--------------------------+--------------------------+

如果有人对如何改进数据库结构有任何建议，我愿意接受优化它的想法。谢谢

以下是 @Shakir 答案的输出

+-------------+-------+-------------+------------------+-------------+
|    title    | price | description |   description    | description |
+-------------+-------+-------------+------------------+-------------+
| Dell XPS 10 |  1000 | Laptop      | Brand            | Dell        |
| Dell XPS 10 |  1000 | Laptop      | Series           | Dell        |
| Dell XPS 10 |  1000 | Laptop      | Color            | Dell        |
| Dell XPS 10 |  1000 | Laptop      | Operating System | Dell        |
| Dell XPS 10 |  1000 | Laptop      | Screen Size      | Dell        |
| Dell XPS 10 |  1000 | Laptop      | Touchscreen      | Dell        |
| Dell XPS 10 |  1000 | Laptop      | Brand            | XPS         |
| Dell XPS 10 |  1000 | Laptop      | Series           | XPS         |
| Dell XPS 10 |  1000 | Laptop      | Color            | XPS         |
| Dell XPS 10 |  1000 | Laptop      | Operating System | XPS         |
| Dell XPS 10 |  1000 | Laptop      | Screen Size      | XPS         |
| Dell XPS 10 |  1000 | Laptop      | Touchscreen      | XPS         |
| Dell XPS 10 |  1000 | Laptop      | Brand            | Black       |
| Dell XPS 10 |  1000 | Laptop      | Series           | Black       |
| Dell XPS 10 |  1000 | Laptop      | Color            | Black       |
| Dell XPS 10 |  1000 | Laptop      | Operating System | Black       |
| Dell XPS 10 |  1000 | Laptop      | Screen Size      | Black       |
| Dell XPS 10 |  1000 | Laptop      | Touchscreen      | Black       |
| Dell XPS 10 |  1000 | Laptop      | Brand            | Windows 10  |
| Dell XPS 10 |  1000 | Laptop      | Series           | Windows 10  |
| Dell XPS 10 |  1000 | Laptop      | Color            | Windows 10  |
| Dell XPS 10 |  1000 | Laptop      | Operating System | Windows 10  |
| Dell XPS 10 |  1000 | Laptop      | Screen Size      | Windows 10  |
| Dell XPS 10 |  1000 | Laptop      | Touchscreen      | Windows 10  |
+-------------+-------+-------------+------------------+-------------+

Answer 1

尝试一下并显示结果

SELECT products.title, products.price, items.description,  
parameters.description, specifications.description
FROM products
INNER JOIN items ON products.itemid = items.id
INNER JOIN parameters ON parameters.itemid = items.id
INNER JOIN specifications ON specifications.productid = products.id
LIMIT 0 , 30