How to write Like Condition within In Clause for thousands of Items

i want to filter Items of table PartMaster based on the input from table PARTINPUT using LIKE condition. Both the table has multiple entries.

Table 1: PartMaster

PartNo
======
Part1.DRW
Part2.DRW
Part3.ASM
Part3.PRT
Part1.XLT
Part2.ASM
Part3.ASM
Part3.DRW
Part4.ASM
Part4.DRW


Part5000.PRT

Table2: PartINPUT

PART
=====
Part1
Part2
Part3
    .
.
.
PART5000

I am trying to find a items from table PartMaster where

select *
from PartMaster PM 
where PM.PartNo in (select PI.PART from PARTINPUT PI 
                    where PM.PartNo like '%PI.PART%')

I have tried:

select *
from PartMaster PM
where PM.PartNo in (select PI.PART from PARTINPUT PI where PM.PartNo like '%PI.PART%)'

and

select *
from PartMaster PM
where PM.PartNo in (select PI.PART from PARTINPUT PI where PM.PartNo like '%' || PI.PART ||'%')'

Results should return all the items from table PartMaster based on matching %part% from second input table.

Answer

If I get this right you want to join on the partno of partmaster beginning with a part of partinput and a dot. (Or maybe without the dot, remove it from the query in that case).

SELECT *
       FROM partmaster pm
            INNER JOIN partinput pi
                       ON pm.partno LIKE pi.part || '.%';

db<>fiddle

Advertisement

Answer