I'm trying to search a field that contains information from another table. I've tried the following queries:
SELECT *
FROM table_1
WHERE text_field LIKE '%'||(SELECT other_text FROM table_2)||'%';
SELECT *
FROM table_1
WHERE text_field ~* '(SELECT other_text FROM table_2)';
Unfortunately, I get:
ERROR: more than one row returned by a subquery used as an expression
Example tables:
table_1
| id | timestamp | text_field |
|---|---|---|
| 100 | 2022-06-01 17:40:00 | Two Transactions completed in 12 seconds |
| 101 | 2022-06-01 17:42:42 | One Transaction completed in 5 seconds |
| 102 | 2022-06-02 03:24:23 | 15 Records created and 4 deleted in 94 seconds |
table_2
| id | other_text |
|---|---|
| 1 | 94 |
| 2 | 12 |
And I want to query table_1 based on text_field containing either 12 or 94, which would give me this return:
| id | timestamp | text_field |
|---|---|---|
| 100 | 2022-06-01 17:40:00 | 2 Transactions completed in 12 seconds |
| 102 | 2022-06-02 03:24:23 | 15 Records created and 4 deleted in 94 seconds |
I've looked at multiple suggestions but they all center around a JOIN, but my text_field won't ever EQUAL my other_text field.
I've looked at arrays, but each entry in table_2 is distinct, not a comma-delimited list. Each text_field entry has the potential to be a varying length as indicated in my example, so I can't just select the 6th 'space-delimited' field in text_field; I have to do some version of "contains" or "like".
Is this even possible?
CodePudding user response：
Use a CTE(Common Table Expression).
WITH ot as(
SELECT other_text FROM table_2)
SELECT *
FROM ot, table_1
WHERE text_field LIKE '%'|| ot.other_text||'%';
To be complete I should mention you can shorten this to:
SELECT *
FROM table2, table_1
WHERE text_field LIKE '%'|| table2.other_text||'%';
UPDATE
I prefer the first form as it makes it clearer to me what is going on.
CodePudding user response：
Joining tables like the currently accepted answer suggests, is typically not what you want. The question is not entirely clear there.
If other_text contains 4, 9, 15, and 94, your row with "15 Records created and 4 deleted in 94 seconds" would be listed in the result four times. Use EXISTS instead to get distinct result rows:
SELECT t1.*
FROM table_1 t1
WHERE EXISTS (SELECT FROM table_2 t2 WHERE t1.text_field ~ t2.other_text)
ORDER BY t1.id; -- optional
With more than a few duplicate matches, it's faster, too.
Related:
If other_text can contain special characters for LIKE or the regexp match ~ (whichever you use) you may want to escape them:
Aside: yes, that's a "join" too:
SELECT * FROM a,b WHERE a.x LIKE b.y;
It's the less readable twin of:
SELECT * FROM a JOIN b ON a.x LIKE b.y;
See: