table1:

table 2:

this table2 also contains multiple rows

case 1: If I use table1.name = ab as a where condition, the output should be, ab, ab_d, aa, bb records

select * from table1 t1 where t1.name = 'ab';

(or) case 2: If I use table2.col2 = ac_d as a where condition, the output should be, ac, ac_d, ss, pp records

select * from table1 t1 where t1.name = 'ac_d';

I am unable to write query....

CodePudding user response：

The 3rd table is identical to the first table. Please provide CREATE TABLE scripts with data types, Primary keys and Foreign key constraints.

Looking at the structure of the 2nd table and the values in col2 in the first table I think you are going to need an UNPIVOT command This will take the 2nd table and use the first column as an entity reference, take the column names from the other columns as use them as attributes and take the column value and use it as the value. E.g. If you had a table:

    Colour    Jan     Feb     Mar
    RED       20      15      12
    BLUE       8       7      15

The UNPIVOT command would produce
    Colour    Month    Amt
    RED       Jan       20
    RED       Feb       15
    RED       Mar       12
    BLUE      Jan        8
    BLUE      Feb        7
    BLUE      Mar       15

CodePudding user response：

You can do:

select y.*
from table2 x
join table1 y on y.name in (x.col1, x.col2, x.col3, x.col4)
where 'ab' in (x.col1, x.col2, x.col3, x.col4);

Result:

 NAME  SAL 
 ----- --- 
 ab    34  
 ab    322 
 ab_d  34  
 ab_d  322 
 aa    34  
 aa    322 
 bb    34  
 bb    322 

See running example at db<>fiddle.