0

I'm trying to figure out a way to find an entry or the entries where my keywords (multiple) are present in the category table / category name column .

CREATE TABLE entry (
  entryid INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  datecreated date NOT NULL
);
CREATE TABLE category (
  entryid INTEGER,
  categoryid INTEGER PRIMARY KEY,
  categoryname TEXT NOT NULL
);

INSERT INTO entry VALUES (1, 'title1', '2022-01-15');
INSERT INTO entry VALUES (2, 'title2', '2022-01-16');
INSERT INTO entry VALUES (3, 'title3', '2022-01-17');


INSERT INTO category VALUES (1, 1, 'categorya');
INSERT INTO category VALUES (1, 2, 'categoryd');
INSERT INTO category VALUES (1, 3, 'categorye');
INSERT INTO category VALUES (2, 4, 'categoryd');
INSERT INTO category VALUES (2, 5, 'categorya');
INSERT INTO category VALUES (2, 6, 'categoryb');
INSERT INTO category VALUES (3, 7, 'categorye');


SELECT DISTINCT entry.* FROM entry,category 
WHERE entry.entryid=category.entryid 
AND (category.categoryname LIKE '%ye%' AND category.categoryname LIKE '%yd%')

I thought that this would give me entryid=1 but, if I unterstand it correctly, I already limit the query with comparing my first keyword? What would I have to do to search for multiple keywords in one column and get the correct entryid?

Edit: I am using HSQLDB within Libre Office Base. I used the data in this post for a more "standardized" approach and to then adjust the solution to my needs :)

l1z4rd
  • 1
  • 1
  • What is your database engine? For example PostgreSQL, MySQL, HSQLDB 1.8. Also, it would help to simplify the question. You should be able to give this example using only two tables and maybe 3 rows. – Jim K Jun 18 '22 at 16:39
  • Do two different checks on the category table. It would look something like `SELECT DISTINCT entry.* FROM entry,partner,category category1, category category2 WHERE entry.entryid=partner.entryid and entry.entryid=category1.entryid AND entry.entryid=category2.entryid AND category1.categoryname LIKE '%ye%' AND category2.categoryname LIKE '%yd%'`. – Jim K Jun 18 '22 at 16:40
  • @JimK Thank you very, very much. I think this solves my problem :) In the meantime I created another solution but without wildcards (if anyone else sees this, it is a variation of a solution from @Gordon Linoff https://stackoverflow.com/a/52257348/14631352 ): `SELECT * FROM entry WHERE entry.entryid= (SELECT entry.entryid FROM entry, category, partner WHERE category.categoryname IN ( 'categorye', 'categoryd' ) AND entry.entryid = category.entryid AND entry.entryid = partner.entryid GROUP BY entry.entryid HAVING COUNT( DISTINCT category.categoryname ) = 2)` – l1z4rd Jun 18 '22 at 22:05

0 Answers0