Click here to Skip to main content
14,389,695 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Everyone,

Good day.

Can you help on how to achieve the result in SQL select query?


Table
dID   dName       sdID
1    XYZ Company   0
2    AA dept       1
3    BB dept       1
4    CC dept       1
5    AA.section    2
6    BB.section    3
7    CC.section    4


Expected query result
dID   dName       sdID     NewColumn
1    XYZ Company   0          
2    AA dept       1      XYZ Company
3    BB dept       1      XYZ Company
4    CC dept       1      XYZ Company
5    AA.section    2      AA dept
6    bb.section    3      BB dept
7    CC.section    4      CC dept 


The sdID will lookup to dID then the corresponding dName shows in NewColumn.

What I have tried:

Can't find related case in the net.
Thanks in advance.
Posted
Updated 5-Nov-19 19:27pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Select 

Select 
dID,
dName,
sdID,
'' NewColumn
from [table] where sdID = 0
union all 
Select 
B.dID,
B.dName,
B.sdID,
A.dName  NewColumn
from [table] A
Left outer join [table] B on (A.dID = B.sdID)
Where B.dID IS NOT NULL
   
v2
Comments
icavs 6-Nov-19 1:36am
   
Hi Manoj Kumar Choubey,
thanks for your time on responding to my query.
Have replicated your solution but I got different result.

dID dName sdID dName
NULL NULL NULL XYZ Company
1 XYZ Company 0 AA dept
1 XYZ Company 0 BB dept
1 XYZ Company 0 CC dept
2 AA dept 1 AA.section
3 BB dept 1 BB.section
4 CC dept 1 CC.section
Manoj Kumar Choubey 6-Nov-19 2:56am
   
I improved the answer.
As per the requirement you can change the query
icavs 6-Nov-19 5:52am
   
Great! It works as needed.
Thanks @Manoj
Manoj Kumar Choubey 6-Nov-19 13:50pm
   
welcome

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100