Finding Records with matching records in another table

Question

I have 2 tables

Table 1

A | B  | C | D | E | F
a   Mi   2   1  4   001
b   Ma   3   1  4   001
c   NA   1   1  4   001
b   Na   3   1  4   001
d   Na   2   1  4   001
a   Mi   2    1  4   002
b   Na   3    1  4   002
c   Ma   1    1  4   002
d   Na   2   1  4   001

Table 2

A | B | C | D | E
a   Mi   2   1  4
b   Ma   3   1  4
c   NA   1   1  4
d   Na   2   1  4

OutPut :

F    |  D  
001    1

So columns A, B, C, D, E and F are all columns that specific conditions in them. Table 1 is the table with data that needs to be compare to data in table2. If all records in different columns except F match from Table1 to the records in Table2, only those records should be selected in the output.

Only 001 from column F is displayed because it has all the 4 rows with the same values in the same columns as given in Table 2. Records with value 002 in column F are not selected because they do not have all the rows in table 2. They do have all 4 rows but the record with b does not have all the same matching values.

The final result need not be the output i have mentioned. It could just be all those rows that match the rows given in Table 2. The output is just what the last step is . I can achieve that if i get all rows that match all the records in table 2 like by like.

Something I tried-

select count(A) over(Partition by A,B,C,D,E,F) as rw,* 
into #temp1 
from Table1

select sum(rw) as sm, F 
from #temp1 group by F

select F 
from #temp
where sm = (select count(A) from Table2)

One of the issues with this logic is that 002 can have 2-3 duplicated rows which might result in the count being equal to the count of rows in table2 .

Answer 1

With a join of the tables and then group by F:

select t1.f, max(t1.d) d
from table2 t2 inner join (select distinct * from table1) t1
on t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D and t1.E = t2.E
group by t1.f
having count(*) = (select count(*) from table2)

I used max(t1.d) as it is not clear if the value of D is the same for each F.
See the demo.
Results:

> f   |  d
> :-- | -:
> 001 |  1

If you want the rows from table1 that match the rows from table2, use a CTE:

with cte as (
  select t1.f
  from table2 t2 inner join (select distinct * from table1) t1
  on t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D and t1.E = t2.E
  group by t1.f
  having count(*) = (select count(*) from table2)
)
select t1.* from table1 t1
where 
  t1.f in (select f from cte)
  and exists (
    select 1 from table2 t2
    where t1.A = t2.A and t1.B = t2.B and t1.C = t2.C and t1.D = t2.D and t1.E = t2.E
  )

See the demo.
Results:

> A  | B  |  C |  D |  E | F  
> :- | :- | -: | -: | -: | :--
> a  | Mi |  2 |  1 |  4 | 001
> b  | Ma |  3 |  1 |  4 | 001
> c  | NA |  1 |  1 |  4 | 001
> d  | Na |  2 |  1 |  4 | 001
> d  | Na |  2 |  1 |  4 | 001

If you want distinct rows use:

select distinct t1.* from table1 t1

instead.
Results:

> A  | B  |  C |  D |  E | F  
> :- | :- | -: | -: | -: | :--
> a  | Mi |  2 |  1 |  4 | 001
> b  | Ma |  3 |  1 |  4 | 001
> c  | NA |  1 |  1 |  4 | 001
> d  | Na |  2 |  1 |  4 | 001

Answer 2

Disregarding the suspect row I mention in the comment, I think this is what you want:

select * 
from [Table 1] t1
where exists
(
    select 1 
    from [Table 2] t2
    where 
            t1.A=t2.A
        and t1.B=t2.B
        and t1.C=t2.C
        and t1.D=t2.D
        and t1.E=t2.E
)