I would like to output as,
Here below rules are places,
1)All the DepNo from table A should come
2)If the DepNo from A is not present in table B then flag should be marked as 'N'
3)if DeptNo is present multiple times in table B (for e.g. 12345 ) then out of I,S,H only H should be picked up also if only S,I is present(for e.g. 12347) then 'S' should be picked up and if any 'I' is present then only 'I' should be picked up.
select distinct t1.depno,
when locate('H',t1.flag)<>0 then 'H'
when locate('S',t1.flag)<>0 then 'S'
when locate('I',t1.flag)<>0 then 'I'
else 'N'end as flag
from (select distinct a.depno,coalesce(b.flag,'N')||coalesce(c.flag,'N')||coalesce(d.flag,'N') as flag from table1 a
left outer join table2 b on a.depno = b.depno and b.flag = 'H'
left outer join table2 c on a.depno = c.depno and c.flag = 'S'
left outer join table2 d on a.depno = d.depno and d.flag = 'I') t1;