MySQL多表查询成一个结果

Question

I have two tables like following

images_table

id   |  image_name   |   image_file
-----+---------------+------------------
1    |  flower       |  rose.jpg
2    |  fish         |  gold_fish.jpg
-----+---------------+----------------

tags_table

tag_id   |   tag_name   |  image_id
---------+--------------+---------------
1        |    rose      |   1
2        |  red flower  |   1
3        |  redfish     |   2
4        |  aquarium    |   2
---------+--------------+------------------

When someone search, I am using the following query

SELECT * FROM images_table WHERE image_name LIKE "%$search"

But I need to show results from both tables. ie combination of following queries

SELECT * FROM images_table WHERE image_name LIKE "%$search"

SELECT * FROM tags_table as tags LEFT JOIN images_table as img ON img.id=tags.image_id WHERE tags.tag_name LIKE "%$search"

How can I get both results as a single array/list? So that I can implement pagination as well.

Answer 1

You can use the following query

SELECT *

FROM images_table m1,
     tags_table m2

WHERE m1.image_table LIKE '%$search' OR m2.tag_name LIKE '%$search'

you just add both of the tables in the FROM part and give them alias thereafter in the where condition you add both of them you can use OR and also AND but that depends, OR will evaluate true if there is 1 match and AND will only will evaluate true if there are matches in both of the tables