从MySQL中的两个表中选择数据 [英] SELECT data FROM two tables in MySQL

问题描述

我所拥有的:下一个结构:

table_zero
-> id (具有自动增量功能的PRIMARY)
->其他

table_zero
-> id (PRIMARY with auto increment)
-> other

表_1
-> id (表零ID的外键)
-> varchar(80)示例值:(aahellobbb)
-> one_field

table_1
-> id (foreign key to table zero id)
-> varchar(80) Example value: (aahellobbb)
-> one_field

表_2
-> id (表零ID的外键)
-> varchar(160)示例值:(aaececehellobbb)
-> other_field

table_2
-> id (foreign key to table zero id)
-> varchar(160) Example value: (aaececehellobbb)
-> other_field

我想要的:搜索并获取一个(id，varchar)数组，该数组包含在varchar字段上具有LIKE'％str％'的所有匹配项.例如，如果我使用"hello"字符串进行搜索，那么我应该获得两个示例值及其各自的ID.这些ID始终是不同的，因为它们是对PRIMARY KEY的引用.

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.

我尝试过的事情:我尝试使用UNION ALL，但在示例中它不适用于LIMITS.

What I tried: I tried with UNION ALL but it does not work with LIMITS in my example.

推荐答案

使用UNION，您可能会获得多次具有相同ID的行.使用LEFT JOIN怎么办?

By using UNION you may get several times rows with the same ID. What about using LEFT JOIN ?

如果我了解您的问题:

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%'

这篇关于从MySQL中的两个表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！