SQL与join一起使用时给出多个记录

我通过以下方式有两本书和作者的表格,

图书表:

    id  |  name   |  author_id |
    ----------------------------
    1   | Java    | 1          |
    1   | Spring  | 1          |
    1   | JSF     | 1          |
    1   | Apache  | 1          |
    1   | Scala   | 1          |
    1   | PHP     | 2          |
    1   | Laravel | 2          |
    1   | Node    | 3          |
    1   | Vue     | 3          |

作者表:

   id   | name           |
   -----------------------
   1    | Gulsan Singh   | 
   2    | Chandan Singh  |
   3    | Charan Putrevu |

我想同时搜索名称和作者字段,所以我使用了以下查询,

    SELECT a.name AS authorName, b.name AS bookName FROM author a LEFT JOIN books b on a.id = b.author_id WHERE 
    a.name LIKE '%Singh%' OR b.name LIKE '%Singh%' LIMIT 5

但是此查询以以下方式返回结果

    authorName   |  bookName   |
    ----------------------------
    Gulsan Singh | Java        |
    Gulsan Singh | Spring      |
    Gulsan Singh | JSF         |
    Gulsan Singh | Apache      |
    Gulsan Singh | Scala       |

Because of this I am missing the second author Chandan Singh since I had LIMIT 5. I want to avoid this duplicacy, but if the search query matches any book names then the authors may be shown several times. But here authors are shown several times when the bookName column does not have match but a single match is available in authorName column.

希望我在这里很清楚,是否有可能实现我的目标?

评论