Search code examples
mysqlselectuniondatabase-table

SELECT data FROM two tables in MySQL

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

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