Hi,
Try
1. Create a view like below. View name is vwABC
select a.id,a.productCode,a.tableDSymptom, d.weight from a,d where a.tableDSymptom = d.id
union all
select b.id,b.productCode,b.tableDSymptom , d.weight from b,d where b.tableDSymptom = d.id
union all
select c.id,c.productCode,c.tableDSymptom ,d.weight from c,d where c.tableDSymptom = d.id
After that use below sql
select vwABCMain.id,vwABCMain.productCode,vwABCMain.tableDSymptom,vwABCMain.weight,
case (select min(vwABCSub.weight) from [dbo].[vwABC] vwABCSub where vwABCSub.productCode = vwABCMain.productCode )
when vwABCMain.weight then 'Y'
ELSE 'N'
END
from [dbo].[vwABC] vwABCMain
order by id