database - Finding duplicate rows in two tables in SQL Server -

i have database 2 tables material , mandatory.

mandatory table contains 2 columns class , characteristic_name.

material table contains 4 columns material_name, class, characteristic_name, , characteristic_value.

i want show material_name has same class, characteristic_name , characteristic_value class, characteristic_name same in mandatory table.

for example

material table contain data

material_name   class   characteristic_name characteristic_value ----------------------------------------------------------------- 000000001       class1  model                       12 000000001       class1  size                         1 000000001       class1  type                        000000002       class1  model                       12 000000002       class1  size                         1 000000002       class1  type                        000000003       class2  type                        b 000000003       class2  weight                      55 

mandatory table contains data:

class   characteristic_name ------------------------------- class1  model class1  size class1  type class2  type class2  weight 

material_name 00000001 , 000000002 same because have same class, characteristic_name , characteristic_value

what query show these results?

this complicated question. if understand correctly, want pairs of materials mandatory fields match. but, mandatory fields vary class, solution of pivoting data not work easily.

with m (       select m.*       (select m.*,                    count(*) on (partition m.material_name) nummandatorymatches,                    nummandatory             material m join                  (select ma.*, count(*) on (partition ma.class) nummandatory                   mandatory ma                  ) ma                  on m.class = ma.class ,                     m.characteristic_name = ma.characteristic_name            ) m       nummandatorymatches = nummandatory     )  select m.material_name, m2.material_name m join      m m2      on m2.class = m.class ,         m2.characteristic_name = m.characteristic_name ,         m2.characteristic_value = m.characteristic_value group m.material_name, m2.material_name having count(*) = max(m.nummandatorymatches); 

the cte gets materials have mandatory fields. main select cross join checks values match.

i should note assumes characteristics not repeated given field -- although modified handle situation.