I have a table "books":
| book_id (PK) | date_added | date_updated |
|---|---|---|
| 100 | 2022-07-30 | 2022-07-31 |
| 101 | 2022-07-28 | |
| 102 | 2022-07-27 | |
| 103 | 2022-07-27 |
and another one "book_details":
| attribute_id (PK) | book_id (FK) | param_1 | param_2 | param_3 | param_4 | param_value |
|---|---|---|---|---|---|---|
| 1 | 100 | A | A | B | C | Corporate and municipal securities |
| 2 | 100 | A | B | B | A | Marva J. Dawn |
| 3 | 101 | A | A | B | C | Lucky |
| 4 | 101 | A | B | B | A | Ruth Haley Barton |
| 5 | 101 | A | C | A | A | 9786029254464 |
| 6 | 102 | A | A | B | C | Truly the community |
| 7 | 103 | A | A | B | C | Index to the library of investment banking |
| 8 | 103 | A | B | B | A | Marva J. Dawn |
In the second table, param_1-4 are required parameters which define the attribute of the book. For example:
param_1: A; param_2: A; param_3: B; param_4: C means "Book's title", and
param_1: A; param_2: B; param_3: B; param_4: A means "Book's author", it means book with ID: 100 has a title of "Corporate and municipal securities" and written by "Marva J. Dawn".
If I search for books with title that contained word: "muni", it will return two book_id: 100 and 102, and if I search for the author "dawn", it will return book_id: 100 and 103. How should I work the query so that when I search for title: "muni" and author: "dawn", it will only return one book_id: 100
So far I tried using this:
SELECT book_id FROM books AS b, book_details AS bd WHERE b.book_id = bd.book_id AND ((param_1 = 'A' AND param_2 = 'A' AND param_3 = 'B' AND param_4 = 'C' AND param_value ILIKE '%muni%') AND (param_1 = 'A' AND param_2 = 'B' AND param_3 = 'B' AND param_4 = 'C' AND param_value ILIKE '