snowflake: dynamic query from another table in same order-CodePudding

I have a table named as table1 and it has just 2 columns in it named cola,colB like below

cola | colb
 1   | abc
 2   | xyz

I have another query i am trying to run, like below

 select * from tableb where name = {} and idno = {}

I want the place holders to fill values from tablea just in same order and after it fills it may look like below

select * from tableb where name = 'abc' and idno = 1
select * from tableb where name = 'xyz' and idno = 2

I was trying to find out if there is any other approach of achieving this apart from subquery

CodePudding user response：

simple join ?

select b.* from table1 a
join table2 b on a.colb = b.name and a.cola = b.idno

but if you want to make the query strings :

select 'select * from tableb where name = '''|| cola ||''' and idnp='|| colb || 
from tablea

CodePudding user response：

Try this

  Select * from tableb b
  Left join table1 a
  On b.name=a.col1
  And b.idno=a.col2

You could also do

  Select * from tableb  where 
  name||idno in (Select col1||col2 from tablea)