First query result to be modified and used in the second query SQL

Question

I have a table1 suppose ABC from which I am getting an o/p from a column like 'MYname_GKS_50' . I want only MYname part of that result to be used as a condition to fetch another column of different table. Suppose If i give columnname = MYname from table2.xyz . These 2 queries should be in a single SQL query in OracleDB. e.g:

Table1 (col1 , col2)

col2 has data MYname_GKS_50,MYname_GKS_51, MYname_GKS_52 , Ora_10, Ora_11...

i want col2 results only the MYname_GKS and Ora part for my search condition , the changing nos are not required.

Table2 (Col3, col4)

the value from col2 i.e. MYname_GKS and Ora should be now compared with col3 of table2. if it matches, it should give me the col4 of the table2.

Any suggestions gurus!

Removed sql-server based off OP asking about OracleDB and column names Ora_10 Ora_11 — scsimon
Database used is Oracle 11g. Ora_10,11 are simply words i used, it were like suppose Order_detailsversionOne_100056 , Order_detailsversionOne_100057, Order_detailsversionOne_100058.. Donot get confused with any ORA errors. — Gopal

Ponder Stibbons Ponder Stibbons · Accepted Answer · 2017-10-04T13:56:48

You could use like in joining condition:

on t1.col2 like t2.col3||'%'

Like here:

with table1 (col1, col2) as (
    select 1, 'MYname_GKS_50' from dual union all
    select 2, 'MYname_GKS_51' from dual union all
    select 3, 'MYname_GKS_52' from dual union all
    select 4, 'Ora_10'        from dual union all
    select 5, 'Ora_11'        from dual ),
table2 (col3, col4) as (
    select 'MYname_GKS', 'XYZ' from dual union all
    select 'Ora',        'PQR' from dual )
select * 
  from table1 t1 join table2 t2 on t1.col2 like t2.col3||'%'

Result:

      COL1 COL2          COL3       COL4
---------- ------------- ---------- ----
         1 MYname_GKS_50 MYname_GKS XYZ
         2 MYname_GKS_51 MYname_GKS XYZ
         3 MYname_GKS_52 MYname_GKS XYZ
         4 Ora_10        Ora        PQR
         5 Ora_11        Ora        PQR

There is a risk that table2 contains more then one matching row for instance MYname and MYname_GKS and I don't know what You want in such situation.

First query result to be modified and used in the second query SQL

1 Answers