Using a WHERE colname NOT LIKE (select * from otherTable)

Question

So in essence what im trying to do is this:

Given TBL1 (around 8,000 rows)

letters
abababa
bcbcbcb
cdcdcdc

and TBL2 (around 2,000 rows)

letters
ab
bc

I want to write a query to find every row in TBL1 that does not contain atleast one of the substrings in TBL2

It has to be using a subquery because the actual code (cant post here bc its sensitive information) is a pretty complex query with a couple of concatenations and trims etc.

Im trying to do this roughly

SELECT * FROM TBL1 WHERE TBL1.letters NOT LIKE '%'|| (SELECT letters FROM TBL2) ||'%'

My intention is to get the following output returned:

letters
cdcdcdc

As the last row does not contain 'ab' or 'bc'.

to get an idea what my code looks like i will enclose with with out any identifying information.

SELECT
  *
FROM
  tbl1
WHERE
  tbl1.col1 NOT LIKE '%' ||(
    SELECT
      LTRIM(
        RTRIM(
          CONCAT(
            REPLACE(
              tbl2.col1,
              CONCAT(tbl2.col2, tbl2.col3),
              ""
            ),
            " ",
            tbl2.col2,
            " ",
            tbl2.col3
          )
        )
      )
    FROM
      tbl2
  ) || '%'

Answer 1

An option could be to create an auxiliary table to index suffixes of the strings from the table TBL1. The tables could look as follows:

CREATE TABLE `TBL1`
(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `letters` VARCHAR(20) NOT NULL
);

TBL1

id	letters
1	abababa
2	bcbcbcb
3	cdcdcdc

CREATE TABLE `TBL2`
(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `letters` VARCHAR(20) NOT NULL,
    CONSTRAINT `tbl2_letters_uindex` UNIQUE (`letters`)
);

TBL2

id	letters
1	ab
2	bc

CREATE TABLE `TBL1SUFFIX`
(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `suffix`  VARCHAR(20) NOT NULL,
    `tbl1_id` INT NOT NULL,
    CONSTRAINT `tbl1suffix_suffix_tbl1_id_uindex`
        UNIQUE (`suffix`, `tbl1_id`),
    CONSTRAINT `tbl1suffix_tbl1_fk`
        FOREIGN KEY (`tbl1_id`) REFERENCES `TBL1` (`id`)
            ON UPDATE CASCADE ON DELETE CASCADE
);

For each word in the table, TBL1 their suffixes are added to the suffix table:

id	suffix	tbl1_id
1	abababa	1
2	bababa	1
3	bcbcbcb	2
4	cbcbcb	2
5	cdcdcdc	3
6	dcdcdc	3

Here, all suffixes of, for example, abababa are

abababa
bababa
ababa
baba
aba
ba
a

However, I excluded the ones that are suffixes of other suffixes.

Given that the suffixes are indexed, if we look up words that contain, for example, cb, the search would be performed on the index of suffixes by starting characters, using pattern matching LIKE 'cb%' instead of LIKE '%cb%', and return the suffix cbcbcb:

id	suffix	tbl1_id
4	cbcbcb	2

Excluding the words in the table TBL1 that are not equal to the received tbl1_id, we get the words that don't contain cb.

The query, in this case, could be:

SELECT
    `TBL1`.`letters`
FROM
    `TBL1`
    LEFT JOIN (
        SELECT
            `TBL1SUFFIX`.`tbl1_id`
        FROM
            `TBL1SUFFIX`
            LEFT JOIN `TBL2`
                ON `TBL1SUFFIX`.`suffix` LIKE CONCAT(`TBL2`.`letters`, '%')
        WHERE
            `TBL2`.`id` IS NOT NULL
    ) `suffix`
        ON `TBL1`.`id` = `suffix`.`tbl1_id`
WHERE
    `suffix`.`tbl1_id` IS NULL;

or:

SELECT
    `TBL1`.`letters`
FROM
    `TBL1`
WHERE
    NOT EXISTS(
        SELECT
            1
        FROM
            `TBL1SUFFIX`
        WHERE
            EXISTS(
                SELECT 1
                FROM
                    `TBL2`
                WHERE
                    `TBL1SUFFIX`.`suffix` LIKE CONCAT(`TBL2`.`letters`, '%')
                    AND `TBL1`.`id` = `TBL1SUFFIX`.`tbl1_id`
            )
    );

Answer 2

It is possible to achieve this. try using the NOT LIKE operator in combination with a subquery that concatenates all the letters from TBL2 into a single string and checks if that string is not present in each row of TBL1

Try this

SELECT *
FROM TBL1
WHERE NOT EXISTS (
  SELECT 1
  FROM TBL2
  WHERE TBL1.letters LIKE '%' || TBL2.letters || '%'
)