SQL giving multiple records when using like with join

Question

I have two tables of books and authors in the following way,

Books table:

    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          |

Author table:

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

I want to search on both name and author fields so I used the following query,

    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

But this query returns back the results in the following way

    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.

Hope I am clear here and is there a possibility to achieve what I am aiming at?

Could you elaborate a bit more.Are you saying you want to set a preference ie> Search first by all distinct authors, followed by all unique books? — George Joseph
Please show us the results that you would expect for this sample data and search criteria. Also, your query has no order by, so it basically returns an undefined set of rows out of the matching ones. — GMB
@GeorgeJoseph Yes, I want the search result for the author to be distinct, but if the search has any match from books then the author list may appear multiple times. But when there is no match in books I dont want the author names to come for every book available on that author. — Charan Putrevu

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-02-07T12:22:05

You can re-order the data before the limit:

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%'
ORDER BY ROW_NUMBER() OVER (PARTITION BY COALESCE(a.name, b.name))
LIMIT 5;

Postgres does not require an ORDER BY for ROW_NUMBER(). If you have a preference on the ordering, you can include one.

SQL giving multiple records when using like with join

1 Answers