Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2015
    Posts
    1

    Unanswered: Inner Join with Same table

    Dear All,

    i have a company table at below.



    HTML Code:
    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

    HTML Code:
    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

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Assuming parentcompany = comid....


    SELECT T.companyname, COALESCE(T2.companyname,'no') as parentcompany
    FROM MyTable T
    LEFT OUTER JOIN MyTable T2
    ON T.parentcompany = T2.comid
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I have a company table at below. <<

    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    Rows are not records. This is a fundamental concept in RDBMS.

    Companies are identified by their DUNS. This is a universal standard, so your com_id is as silly as throwing out the Metric system to invent your own. Being a subsidiary of a company is a relationship among compan ies. Relationships get their own tables. Here is a correction of your posting:

    CREATE TABLE Companies
    (duns CHAR(9) NOT NULL PRIMARY KEY,
    company_name CHAR(35) NOT NULL);

    CREATE TABLE Subsidiaries
    (parent_duns CHAR(9) NOT NULL REFERENCES Companies,
    subsidiary_duns CHAR(9) NOT NULL REFERENCES Companies ,
    PRIMARY KEY (parent_duns, subsidiary_duns));

    >> so here the second [sic] record [sic] I have parent company = 1 meaning company test [sic] as parent for textxx , if a company has parent_company as 0 means tat has no parent company. <<

    It looks like you used assembly language bit flags! We do not do that in SQL. Tests are not predicates. You have failed to normalize anything and increased redundancy. Rows have no ordering, so there is no concept of “second record” in SQL.

    >> so in this I need have a result to display in grid is <<

    Grid? We have no “grid” in SQL because we do not do any display formatting in SQL; that is done in a presentation layer of the tiered server architecture.

    >> please advice. <<

    Stop trying to program until you have read at least one introductory book. Seriously. You are making conceptual errors and forums can only help with programming problems. Learn what First Normal Form (1NF) is, how to write simple DDL and how modern tiered server architectures work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •