|
View previous topic :: View next topic
|
| Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Hello Team,
I have below two tables,
A
| Code: |
DepNo
---------------
12345
12346
12347 |
B
| Code: |
DepNo flag
--------------- -------
12345 H
12345 H
12345 S
12345 I
12347 I
12347 S |
I would like to output as,
output
| Code: |
DepNo Flag
-------------- -------
12345 H
12346 N
12347 S |
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.
Appreciate your suggestion. |
|
| Back to top |
|
 |
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Rohit,
You may try this.
| Code: |
select distinct t1.depno,
case
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; |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select A.deptno,
coalesce(substr(min(digits(locate(b.flag,'HSI'))||b.flag),11),'N')
from TableA A
left join TableB B on a.deptno = b.deptno
group by a.deptno |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Thanks Srihari.
GuyC, it is working as expected. Thank you. |
|
| Back to top |
|
|
|
|
