Inner Join With Same Table.. - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inner Join With Same Table..

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources