Inner Join with Same Table

Question

User485912165 posted

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,

Muthu

Answer 1

User-1716253493 posted

SELECT companytable.companyname, companytable_1.companyname AS parentcompany
FROM     companytable LEFT OUTER JOIN
                  companytable AS companytable_1 ON companytable.parentcompany = companytable_1.comid

Answer 2

User1577371250 posted

SELECT c.CompanyName, (CASE WHEN c1.CompanyName  IS NULL THEN 'No' ELSE c1.CompanyName END) AS ParentCompany
FROM Company c LEFT OUTER JOIN Company AS c1 ON c.ParentCompany = c1.ComId

Answer 3

User1644755831 posted

Hi mk07,

Please use Left join.

SELECT
  C.companyname,
  CASE WHEN PC.comid IS NULL THEN 'NO' ELSE PC.companyname END parentcompany 
FROM
 dbo.Company C 
LEFT JOIN dbo.Company PC --parentcompany (make a left join so you don't miss any company)
ON C.comid = PC.parentcompany

Hope this helps.

With Regards,

Krunal Parekh