sql server - Match SQL Tables and find if entry exist in each group -

thanks response if scenario extended add third group_id 2 groupnames picks . please me in writing query match 2 tables , find group id in entries of second table match.

table 1

group_id   groupnames     groupvalues     111          g1                  111          g1              b     111          g1              c     111          g2              d     111          g2              e     111          g2              f     111          g3              g     222          g1                  222          g1              b     222          g1              c     222          g2              e     222          g2              f     222          g3              g 333          g3              g 333          g1              b 333          g1              c 

table 2:

groupvalues     b     d     g h 

output

111 

the output of query should "111" since has atleast 1 entry 3 group names "g1,g2,g3" . "222" missing entry group name g2 not returned. please assist.

you can aggregation , having clause:

select t1.group_id table1 t1 inner join      table2      on t2.groupvalues = t1.groupvalues group t1.group_id having count(distinct t1.groupvalues) = (select count(distinct groupvalues) table2); 

note distinct not necessary if know there no duplicate values.