从MySql中的两个表中选择数据

What I have: The next structure:

我有什么:下一个结构:

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

table_zero - > id(带自动增量的PRIMARY) - >其他

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... I should get both example values with their respective ids. These ids are allways 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。

3 个解决方案

#1

By using UNION you 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%'

#2

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

#3

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

#1