PHP MySQL将用户表加入多列和多行

瓦迪洛夫

对于我正在研究的项目，在将表连接到表格时，我遇到了一些问题。

我有3张桌子，一张用于项目，一张用于客户，一张用于用户。我试图将“客户”和“用户”的ID存储在“项目”表中，并在检索时将它们加入。

+----+--------------+
| ID | CustomerName |
+----+--------------+
|  1 | Customer 1   |
|  2 | Customer 2   |
|  3 | Customer 3   |
+----+--------------+

+----+-----------+----------+
| ID | FirstName | LastName |
+----+-----------+----------+
|  1 | Bob       | Belcher  |
|  2 | Stirling  | Archer   |
|  3 | Bart      | Simpson  |
|  4 | Peter     | Griffin  |
|  5 | BoJack    | Horseman |
|  6 | Eric      | Cartman  |
+----+-----------+----------+

+----+---------------+-----------------+-------------+-------------------+-------------------+--------------------+
| ID | ProjectNumber | ProjectCustomer | ProjectLead | ProjectElectrical | ProjectMechanical | ProjectDescription |
+----+---------------+-----------------+-------------+-------------------+-------------------+--------------------+
|  1 | 0001          |               1 |           3 |                 4 |                 6 | Project 1          |
|  2 | 0002          |               2 |           2 |                 5 |                 5 | Project 2          |
|  3 | 0003          |               3 |           1 |                 6 |                 4 | Project 3          |
+----+---------------+-----------------+-------------+-------------------+-------------------+--------------------+

我整天都在忙于Select的搜索，这是我所能进行的搜索：

select Projects.ProjectNumber, Customers.CustomerName, CONCAT_WS(' ', Users.FirstName, Users.LastName) AS ProjectLead, Projects.ProjectElectrical, Projects.ProjectMechanical, Projects.ProjectDescription FROM Projects
INNER JOIN Customers ON Projects.ProjectCustomer = Customers.ID
LEFT JOIN Users ON Projects.ProjectLead = Users.ID

这使我成为其中的一部分：

+---------------+--------------+-----------------+-------------------+-------------------+--------------------+
| ProjectNumber | CustomerName | ProjectLead     | ProjectElectrical | ProjectMechanical | ProjectDescription |
+---------------+--------------+-----------------+-------------------+-------------------+--------------------+
| 0001          | Customer 1   | Bart Simpson    |                 4 |                 6 | Project 1          |
| 0002          | Customer 2   | Stirling Archer |                 5 |                 5 | Project 2          |
| 0003          | Customer 3   | Bob Belcher     |                 6 |                 4 | Project 3          |
+---------------+--------------+-----------------+-------------------+-------------------+--------------------+

但是对于我一生来说，我无法让ProjectElectrical和ProjectMechanical做到与ProjectLead相同的功能。我要么获得ProjectLead的重复副本，要么得到NULL。

谁能帮助我指出正确的方向？我需要完全重新设计查询还是在正确的轨道上？

我在SQL Fiddle上摆弄过

预先感谢您的所有答复！

亚历克斯

这是小提琴中更新后的查询：

select Projects.ProjectNumber, Customers.CustomerName, CONCAT_WS(' ', Users.FirstName, Users.LastName) AS ProjectLead, CONCAT_WS(' ', u2.FirstName, u2.LastName) AS ProjectElectrical, CONCAT_WS(' ', u3.FirstName, u3.LastName) AS ProjectMechanical, Projects.ProjectDescription FROM Projects
INNER JOIN Customers ON Projects.ProjectCustomer = Customers.ID
LEFT JOIN Users ON Projects.ProjectLead = Users.ID
LEFT JOIN Users AS u2 ON Projects.ProjectElectrical = u2.ID
LEFT JOIN Users AS u3 ON Projects.ProjectMechanical = u3.ID

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。