Geek Answers Handbook

SELECT data from two tables in MySql

What I have: The following structure:

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

table_1
-> id (foreign key for null table identifier)
-> varchar (80) Example value: (aahellobbb)
-> one_field

table_2
-> id (foreign key for null table identifier)
-> varchar (160) Example value: (aaececehellobbb)
-> other_field

What I want: Find and get an array (id, varchar) containing all matches with LIKE '% str%' in the varchar field. For example, if I look for the string "hello" ... I should get both example values ​​with their identifiers. These identifiers will always be different, as they are links to the PRIMARY KEY.

What have I tried . I tried to use UNION ALL, but in my example it does not work with LIMITS.

+8
mysql select union database-table
source share
3 answers

Using UNION , you can get multiple lines with the same identifier. How about using a LEFT JOIN ?

If I 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%'
+9
source share

try it

 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%'
+2
source share
 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
source share

More articles:


All Articles