MySQL Full Text Search - multiple search for the same field

I have these table:

|   ID   | detail_id |       detail_value       | book_id |

| ------ | --------- |------------------------- | ------- |

| 1      | 11        | Warszawa                 | 103     |

| 2      | 14        | Grażyńskiego             | 123     |

| 3      | 11        | st.Warszawa m            | 123     |

| 4      | 14        | Michała Grażyńskiego     | 222     |

| 5      | 11        | Warszawa                 | 222     |

| 6      | 14        | Katowicka                | 103     |

| 7      | 9         | 923                      | 23      |

| 8      | 12        | 432424                   | 23      |

| 9      | 14        | Przykładowa              | 23      |

| 10     | 11        | Warszawa                 | 23      |

detail_id: 14 - this is street name, 11 - this is city name (other is not important).

And now, I have two phrases (which are writed by user on my website): warszawa (detail_id: 11 - this is city name) and grażyń (detail_id: 14 - this is the part of street name).

And now I want to get all records where book_id is 123 and 222, because these records contains phrase warszawa as detail_id = 11 and phrase grażyń as detail_id = 14.

Here is my sql request:

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) AND (detail_id = 14 AND MATCH (detail_value) AGAINST ('*grażyń*' IN BOOLEAN MODE))

The problem is - this request return no results. When I try use part of this query:

SELECT * FROM `table` WHERE detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)

OR:

SELECT * FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*grażyń*' IN BOOLEAN MODE)

this works fine but when I want to combine these queries, I got no results.

Solution

You can use extss but youtrr query actually gives back zero rows back

CREATE TABLE `table` (
  `ID` INTEGER,
  `detail_id` INTEGER,
  `detail_value` VARCHAR(20),
  `book_id` INTEGER
  ,
       FULLTEXT idx (detail_value)
);

INSERT INTO `table`
  (`ID`, `detail_id`, `detail_value`, `book_id`)
VALUES
  ('1', '11', 'Warszawa', '103'),
  ('2', '14', 'Grażyńskiego', '123'),
  ('3', '11', 'st.Warszawa m', '123'),
  ('4', '14', 'Michała Grażyńskiego', '222'),
  ('5', '11', 'Warszawa', '222'),
  ('6', '14', 'Katowicka', '103'),
  ('7', '9', '923', '23'),
  ('8', '12', '432424', '23'),
  ('9', '14', 'Przykładowa', '23'),
  ('10', '11', 'Warszawa', '23');

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) 
AND EXISTS (SELECT 1 FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*rażyń*' IN BOOLEAN MODE))

ID | detail_id | detail_value | book_id
-: | --------: | :----------- | ------:

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 1 |        11 | Warszawa      |     103
 3 |        11 | st.Warszawa m |     123
 5 |        11 | Warszawa      |     222
10 |        11 | Warszawa      |      23

SELECT 1 as test FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('*rażyń*' IN BOOLEAN MODE)

| test |
| ---: |

db<>fiddle here

changing the second query

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) 
AND EXISTS (SELECT 1 FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE))

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 1 |        11 | Warszawa      |     103
 3 |        11 | st.Warszawa m |     123
10 |        11 | Warszawa      |      23

SELECT * FROM `table` WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE))

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 1 |        11 | Warszawa      |     103
 3 |        11 | st.Warszawa m |     123
 5 |        11 | Warszawa      |     222
10 |        11 | Warszawa      |      23

SELECT 1 as test FROM `table` WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE)

| test |
| ---: |
|    1 |
|    1 |

db<>fiddle here

Ok as further constraint both have to have the same bookng _id

SELECT * FROM `table` t1 
WHERE (detail_id = 11 AND MATCH (detail_value) AGAINST ('*warszawa*' IN BOOLEAN MODE)) 
AND EXISTS (SELECT 1 
FROM `table` 
WHERE detail_id = 14 AND MATCH (detail_value) AGAINST ('grażyń*' IN BOOLEAN MODE)
AND book_id = t1.book_id)

ID | detail_id | detail_value  | book_id
-: | --------: | :------------ | ------:
 3 |        11 | st.Warszawa m |     123
 5 |        11 | Warszawa      |     222

db<>fiddle here