SELECT data FROM two tables in MySQL

Question

What I have: The next structure:

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

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

table_2
-> id (foreign key to table zero id)
-> varchar(160) Example value: (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.

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

Solution 1

By using UNION you may get several times rows with the same ID. What about using 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%'

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

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

Comments

[Mark Tower]

Thanks for answering. It works fine for me but I get a pair of two fields called "name". Each pair has one NULL value and the matched string. Is this normal? I know its due to the double table search... but, it just seems strange. Thanks for your answer ;)

[Frosty Z]

If you would like only one field in your results containing the matched string, you can use COALESCE(table_1.varchar_field, table_2.varchar_field) AS matched_string instead of table_1.varchar_field, table_2.varchar_field

[Mark Tower]

Thanks, but Frostys one was the definitive one ;D Vote-up anyway!

[e-info128]

Excesive use of memory, select 200.000 rows limit 0, 10 in 20 seconds when intent order by one column.