Search code examples
sqlpostgresqlsql-like

Is it possible in SQL to match a LIKE from a list of records in a subquery?

Using these tables, as a sample:

CodeVariations:

CODE
-----------
ABC_012
DEF_024
JKLX048

RegisteredCodes:

CODE       AMOUNT
--------   ------
ABCM012         5
ABCK012        25
JKLM048        16

Is it possible to write a query to retrieve all rows in RegisteredCodes when CODE matches a pattern in any row of the CodeVariations table? That is, any row that matches a LIKE pattern of either 'ABC_012', 'DEF_024' or 'JKLX048'

Result should be:

CODE       AMOUNT
--------   ------
ABCM012         5
ABCK012        25

I'm using PostgreSQL, but it would be interesting to know if it's possible to do this in a simple query for either PostgreSQL or any other DB.

Solution

  • Does this do what you need?

    select distinct RC.* from RegisteredCodes RC, CodeVariations CV
where RC.CODE LIKE CV.CODE;