0

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Kim Wilkinson
  • 85
  • 1
  • 1
  • 7

2 Answers2

2

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28