从 MySQL 中的两个表中选择数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15113480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT data FROM two tables in MySQL
提问by Mark Tower
What I have:The next structure:
我有什么：下一个结构：
table_zero
-> id(PRIMARY with auto increment)
-> other
table_zero
-> id(PRIMARY with auto increment)
-> 其他
table_1
-> id(foreign key to table zero id)
-> varchar(80) Example value: (aahellobbb)
-> one_field
table_1
-> id (表零 id 的外键)
-> varchar(80) 示例值：(aahellobbb)
-> one_field
table_2
-> id(foreign key to table zero id)
-> varchar(160) Example value: (aaececehellobbb)
-> other_field
table_2
-> id (表零 id 的外键)
-> varchar(160) 示例值：(aaececehellobbb)
-> other_field
What I want:Search and get an (id,varchar) array containing all matches with the LIKE '%str%' on the varchar field. For example, if I search with the "hello" string, then I should get both example values with their respective ids. These ids are always going to be different, since they are references to a PRIMARY KEY.
我想要什么：搜索并获取一个 (id,varchar) 数组，其中包含 varchar 字段上 LIKE '%str%' 的所有匹配项。例如，如果我使用“hello”字符串进行搜索，那么我应该使用各自的 id 获取两个示例值。这些 id 总是不同的，因为它们是对 PRIMARY KEY 的引用。
What I tried: I tried with UNION ALL but it does not work with LIMITS in my example.
我尝试过的：我尝试过使用 UNION ALL，但在我的示例中它不适用于 LIMITS。
回答by Frosty Z
By using UNIONyou may get several times rows with the same ID. What about using LEFT JOIN?
通过使用，UNION您可能会多次获得具有相同 ID 的行。怎么用LEFT JOIN？
If I've understood your question:
如果我已经理解你的问题：
SELECT table_zero.id, table_1.varchar_field, table_2.varchar_field
FROM table_zero
LEFT JOIN table_1 ON table_zero.id = table_1.id
LEFT JOIN table_2 ON table_zero.id = table_2.id
WHERE table_1.varchar_field LIKE '%str%'
OR table_2.varchar_field LIKE '%str%'
回答by JudgeProphet
Try this
尝试这个
SELECT *
FROM
(
SELECT table_zero.id AS ID, table_1.varchar_field AS field
FROM table_zero
JOIN table_1 ON table_zero.id = table_1.id
WHERE table_1.varchar_field LIKE '%str%'
UNION
SELECT table_zero.id, table_2.varchar_field AS field
FROM table_zero
JOIN table_2 ON table_zero.id = table_2.id
) tbl
WHERE
tbl.field LIKE '%str%'
回答by Amal joseph
SELECT table_zero.id, table_1.varchar_field, table_2.varchar_field
FROM table_zero
LEFT JOIN table_1 ON table_zero.id = table_1.id
LEFT JOIN table_2 ON table_zero.id = table_2.id
WHERE table_1.varchar_field LIKE '%str%'
OR table_2.varchar_field LIKE '%str%'
