0

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
  ) || '%'
4
  • Mr.Smith something like 10,000 rows Feb 2, 2023 at 22:32
  • 1
    TBL1 around 8000 and TBL2 around 2000 Feb 2, 2023 at 22:52
  • My gut says that it will take 16,000,000 tests. Do you have a time constraint? And/or, will the size of the tables grow?
    – Rick James
    Feb 3, 2023 at 2:26
  • Im trying to do this roughly Your logic is incorrect. The subquery returns a rowset, not a scalar (single) value which will cause an error.
    – Akina
    Feb 3, 2023 at 6:21

2 Answers 2

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`
            )
    );
1

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 || '%'
)
2
  • 1
    But why. You could improve your answer by explaining. Feb 3, 2023 at 21:47
  • You are not using NOT LIKE though.
    – Andriy M
    Feb 8, 2023 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.