CODEWITHSUNDEEP
mysqlselectuniondatabase-table
Mark Tower
Mark Tower

Reputation: 559

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.

Upvotes: 12

Views: 84256

Answers (3)

Amal joseph
Amal joseph

Reputation: 31

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

Upvotes: 3

JudgeProphet
JudgeProphet

Reputation: 1729

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

Upvotes: 2

Maxime Pacary
Maxime Pacary

Reputation: 23011

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

Upvotes: 18

Related Questions