Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mk77
Starting Member
4 Posts |
 Posted - 2015-02-26 : 21:19:22
|
Dear All,
i have a company table at below.
comid companyname parentcompany maincom
------- ----------- --------------
1 test 0 1
2 testxx 1 0
so here the second record i have parent company = 1 meaning company test as parent for textxx , if a company has parentcompany as 0 means tat has no parent company.
so in this i need have a result to display in grid is
companyname parentcompany
test no
testxx test
i tired with inner join , but it is only select the second record as it's skip the first record due inner join with comid .
please advice.
Looking for your valuable reply
Regards
Mk
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-02-27 : 01:43:28
|
[code]
;WITH COM
AS
(SELECT 1 AS comID ,'test' AS companyname ,0 AS parentcompany , 1 AS maincom
UNION ALL SELECT 2 ,'testxx',1,0
)
SELECT
pCom.CompanyName
,CASE WHEN cCom.CompanyName IS NULL THEN 'No' ELSE cCom.CompanyName END AS ParentName
FROM com AS pCom
LEFT JOIN com AS cCom
ON pCom.parentCompany = cCom.comID
[/code]
output:
[code]
CompanyName ParentName
test No
testxx test
[/code]
sabinWeb MCP |
 |
|
|
mk77
Starting Member
4 Posts |
Posted - 2015-02-27 : 02:14:46
|
Dear StepSon,
thanks a lot for you reply as i understand using left join instead of inner join will help.
Regards,
Mk
|
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-02-27 : 02:42:42
|
Yes, left join is suitable for this.
Regards,
Sab
sabinWeb MCP |
|
|
|
|
|
|
Inner Join With Same Table..