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.

score 17 · Accepted Answer · answered Feb 27 '13 at 13:43

17

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

answered Feb 27 '13 at 13:43

Maxime Pacary

22,336
11
85
113

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 ;) – Mark Tower Feb 27 '13 at 23:47
2

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` – Maxime Pacary Feb 28 '13 at 09:02

score 3 · Answer 2 · edited Mar 18 '14 at 11:35

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

edited Mar 18 '14 at 11:35

zessx

68,042
28
135
158

answered Mar 18 '14 at 11:18

Amal joseph

31
1

score 2 · Answer 3 · answered Feb 27 '13 at 13:59

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

answered Feb 27 '13 at 13:59

JudgeProphet

1,687
3
26
44

Thanks, but Frostys one was the definitive one ;D Vote-up anyway! – Mark Tower Feb 28 '13 at 15:03
Excesive use of memory, select 200.000 rows limit 0, 10 in 20 seconds when intent order by one column. – e-info128 Apr 25 '14 at 16:51

SELECT data FROM two tables in MySQL

3 Answers3

Linked