Thread: Inner Join with Same table
02-27-15, 01:23 #1Registered User
- Join Date
- Feb 2015
Unanswered: Inner Join with Same table
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 .
Looking for your valuable reply
02-27-15, 08:25 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
Assuming parentcompany = comid....
SELECT T.companyname, COALESCE(T2.companyname,'no') as parentcompany
FROM MyTable T
LEFT OUTER JOIN MyTable T2
ON T.parentcompany = T2.comidIf 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.
02-27-15, 15:57 #3Registered User
Provided Answers: 1
- Join Date
- Jan 2013
>> 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.