mysql - 从多个表中获取行

Question

如果我需要从 products 和 subcontent 表中获取行，我需要编写什么样的查询，其中 image_id 字段匹配 $id?

图像表

mysql> show columns from images;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(10)      | NO   | PRI | NULL    | auto_increment |
| file_name | varchar(255) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

mysql> select * from images;
+----+-------------+
| id | file_name   |
+----+-------------+
|  1 | image_1.png |
|  2 | image_2.png |
|  3 | image_3.png |
|  4 | image_4.png |
+----+-------------+

产品表

mysql> show columns from products;
+----------+-----------------------+------+-----+---------+----------------+
| Field    | Type                  | Null | Key | Default | Extra          |
+----------+-----------------------+------+-----+---------+----------------+
| id       | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| title    | text                  | NO   |     | NULL    |                |
| image_id | int(10) unsigned      | YES  |     | NULL    |                |
+----------+-----------------------+------+-----+---------+----------------+

mysql> select * from products;
+----+-----------------+----------+
| id | title           | image_id |
+----+-----------------+----------+
|  1 | Product Title 1 | 2        |
|  2 | Product Title 2 | 1        |
+----+-----------------+----------+

子内容表

mysql> show columns from subcontent;
+----------+------------------------+------+-----+---------+----------------+
| Field    | Type                   | Null | Key | Default | Extra          |
+----------+------------------------+------+-----+---------+----------------+
| id       | int(10) unsigned       | NO   | PRI | NULL    | auto_increment |
| title    | varchar(255)           | NO   | MUL | NULL    |                |
| image_id | mediumint(10) unsigned | NO   |     | NULL    |                |
+----------+------------------------+------+-----+---------+----------------+

mysql> select * from subcontent;
+----+--------------------+----------+
| id | title              | image_id |
+----+--------------------+----------+
|  1 | SubContent Title 1 | 2        |
|  2 | SubContent Title 2 | 3        |
+----+--------------------+----------+

如果$id=2我要找的结果

+---------------------+
| title               |
+---------------------+
| Product Title 1     |
| SubContent Title 1  |
+---------------------+

Answer 1

您可以使用 UNION ALL语法:

SELECT title FROM products WHERE image_id = 2
UNION ALL
SELECT title FROM subcontent WHERE image_id = 2

UNION is used to combine the result from multiple SELECT statements into a single result set.