MySql 按书名和作者名选择书籍

Question

我有3张 table ，1)书籍

+----+--------------+------+
| id | name         | code |
+----+--------------+------+
|20  | Lucian house | 2214 +
|21  | Lucian mom   | 2214 |
|22  | GoT          | 2214 |
+----+--------------+------+

2) 产品作者

+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|1   | 20      |    5      |
|2   | 21      |    5      |
|3   | 22      |    7      |
+----+---------+-----------+

3) 作者

+----+--------------+
| id | name         |
+----+--------------+
| 5  | Marta        |
+----+--------------+
| 7  | Lucian       |
+----+--------------+

我想选择名称中包含 Lucian 的所有书籍，以及作者为“Lucian”的所有书籍，我该怎么做？我需要使用子查询吗？到目前为止我已经有了这个(我使用 yii 框架)，这就是标准的样子:

SELECT * 
FROM books t 
LEFT JOIN products_authors a ON a.id_book = t.id 
LEFT JOIN authors b ON b.id = a.author_id 
WHERE t.name LIKE %Lucian% 
OR b.name LIKE %Lucian%

Answer 1

试试这个 -

SELECT b.id, b.name, a.name
FROM books b
INNER JOIN products_authors pa ON pa.book_id = b.id 
INNER JOIN authors a ON a.id = pa.author_id
WHERE b.name LIKE %Lucian% 
OR a.name LIKE %Lucian%