search for multiple values (present in table 1) in table 2 col

[varajan]

Can somebody help with the following ?

Input Table

table 1:
field1 --> text
field2 --> text
....

table 2:
fields1 --> text

Output Table

table 3:
field1 --> text
field2 --> text
field3 --> text

Requirement:
for each row in table1, I need to search for table1.field1 and table1.field2 in table2.field1, and if the search is successful then in table 3, I need to populate the following:
table3.field1 = table1.field1
table3.field2 = table1.field2
table3.field3 = table1.field3

 

[r937]

insert 
  into table3 
     ( field1
     , field2
     , field3 )
select field1
     , field2
     , field3
  from table1
 where exists 
     ( select * 
         from table2
        where field1 = table1.field1 )
   and exists 
     ( select * 
         from table2
        where field1 = table1.field2 )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)