locked
Inner Join with Same Table RRS feed

  • 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

    Friday, February 27, 2015 1:25 AM

All replies

  • 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

    Friday, February 27, 2015 3:06 AM
  • 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

    Friday, February 27, 2015 3:22 AM
  • 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

    Wednesday, March 4, 2015 3:22 AM