Output true/false if value includes a keyword from another table-CodePudding

I want to output a column which indicates if the value in offers.heading includes a keyword from keyword.keyword.

Table offers

| heading  |
| -------- |
| first one should be true 
| second one should be false

Table keyword

| keyword |
| --------|
| first   |
| true    |

Result:

| heading                     | keyword         |
| --------                    | --------------  |
| first one should be true    | true            |
| second one should be false  | false           |

The best I could come up with is using join, this however outputs multiple matches and not in true/false format

SELECT
    offers.heading,
    match.keyword
FROM offers
INNER JOIN (
    SELECT keyword
    FROM keyword
) AS match(keyword) ON offers.heading LIKE '%' || match.keyword || '%'


| heading                     | keyword         |
| --------                    | --------------  |
| first one should be true    | true            |
| first one should be true    | first           |
| second one should be false  | null            |

CodePudding user response：

We can try to use EXISTS in SELECT instead of JOIN because that might match more than one row by first one should be true

SELECT
    o.heading,
    EXISTS(SELECT 1 FROM keyword k WHERE o.heading LIKE '%' || k.keyword || '%')
FROM offers o

if you want to get True or False string value we can use CASE WHEN expression.

SELECT
    o.heading,
    (CASE WHEN EXISTS(SELECT 1 FROM keyword k WHERE o.heading LIKE '%' || k.keyword || '%') THEN 'True' ELSE 'False' END) keyword
FROM offers o

sqlfiddle

CodePudding user response：

It is easier to join using ~ which is a regex like. We can use an assertion keyword is not null which returns true or false. Distinct avoids having more than one row per heading.

create table headings (heading varchar(285)); 
insert into headings values('first one should be true'),('second one should be false'),('third one no match');
create table keywords(keyword varchar(10));
insert into keywords values('first'),('true');
select

create table headings (heading varchar(285)); 
insert into headings values('first one should be true'),('second one should be false'),('third one no match');
create table keywords(keyword varchar(10));
insert into keywords values('first'),('true');

select distinct
  heading,
  keyword is not null  keyword
from headings
left join keywords
on heading ~ keyword
;

heading                    | keyword
:------------------------- | :------
first one should be true   | t      
second one should be false | f      
third one no match         | f

db<>fiddle here