mysql - 构造查询以获取基于单个字段的所有匹配项

Question

我有一个数据库，其中包含两个表、链接和游览。游览定义为

+----+-------------+-----+-----+--------+---------+-------+---------+
| ID | FileName    | Lat | Lon | Name   | Heading | Width | Height  |
+----+-------------+-----+-----+--------+---------+-------+---------+

链接定义为

+-----+------------+--------+
| ID  | locationID | linkID |
+-----+------------+--------+

links.locationID 是tour.ID 的外键。我想要做的是获取与特定 tour.name

匹配的所有链接

现在，我必须运行这两个查询并使用第一个查询的 ID 作为第二个查询的变量

SELECT * FROM tour WHERE tour.name = 'name'

SELECT * FROM tour
  INNER JOIN links ON tour.ID = links.linkID 
  WHERE links.locationID = ID of first query

如何使其适应单个搜索？

我尝试过子查询:

SELECT * FROM tour 
  INNER JOIN links ON tour.ID = links.linkID 
  WHERE links.locationID = ( SELECT * FROM tour WHERE tour.name = 'name')

这给了我一个错误 1241，指出操作数应该包含一列。

我查找了一些子查询的示例，但是它们都缺少我正在使用的内部连接语句。此查询需要进行哪些修改？

Answer 1

SELECT links.*, tour.*
FROM links
JOIN tour ON tour.ID = links.linkID
JOIN tour AS location ON location.ID = links.locationID -- you must create an alias, in order to refer to this table without ambiguity
WHERE location.name = 'name';

对于带有子查询的版本，您可以这样做:

SELECT * FROM tour 
INNER JOIN links ON tour.ID = links.linkID 
WHERE links.locationID = (
    SELECT ID -- "operand should contain one column"
    FROM tour WHERE tour.name = 'name'
);

但更喜欢第一个选项。