MySQL 子查询在一张表中两次使用相同的 id 字段进行描述

Question

选择 move.idItem, item.description, （ SELECT location.location_name as movingFrom FROM move, location WHERE move.idlocationFrom = 位置.idlocation ) AS 移动自， （ SELECT location.location_name as movingTo FROM move, location WHERE move.idlocationTo = location.idlocation ) AS 移至 来自移动、项目 WHERE move.idItem = item.idItem

我试图使用上述查询获取移至和移自位置的名称，这会在 PHPMyAdmin 中产生“#1242 - 子查询返回超过 1 行”错误。

描述 项目移动存储在名为“移动”的表中。可以将项目从一个位置移动到另一位置，存储位置 ID（存储在“位置”表中的位置名称）和存储在“项目”表中的项目名称。 movingTo 和 movingFrom 将存储位置表中的 id。

有人可以帮我解决这个问题吗？ 提前致谢

Answer 1

我想你可能想使用连接而不是子查询。像这样的东西：

SELECT move.idItem, 
       item.description, 
       location1.location_name movedFrom, 
       location2.location_name as movedTo
FROM move 
INNER JOIN location location1 ON move.idlocationFrom = locatio1n.idlocation
INNER JOIN location location2 ON move.idlocationTo = location2.idlocation
INNER JOIN item ON move.idItem = item.idItem

Answer 2

Table 1 -  cpe Table

|id | name
|----------
| 1 | cat
| 2 | dog
| 3 | mouse
| 4 | snake
-----------

Table 2 - AutoSelect

|id  | name   |  cpe1_id |  cpe2_id  | cpe3_id |
|-----------------------------------------------
| 1  | user1  |  1       |  3        | 4       |
| 2  | user2  |  3       |  1        | 2       |
| 3  | user3  |  3       |  3        | 2       |
| 4  | user4  |  4       |  2        | 1       |
------------------------------------------------

I would like to see an output of

user1 | cat   | mouse  |  snake  |
user2 | mouse | snake  |  dog    | 
..etc

 SELECT a.name, cpe1.name, cpe2.name, cpe3.name FROM AutoSelect as a
 LEFT JOIN cpe as cpe1 ON ( cpe1.id = a.cpe1_id ) 
 LEFT JOIN cpe as cpe2 ON ( cpe2.id = a.cpe2_id ) 
 LEFT JOIN cpe as cpe3 ON ( cpe3.id = a.cpe3_id )