Help Needed in Join - 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)

Help Needed in Join

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-11 : 19:20:00

Hi,

Below is my table structure

Create Table Company(IdCompany int  primary key,CompanyName varchar(50),CompanyCode varchar(50))

 Insert into Company values(1000,'IBM','CP100'),(1001,'Informix Corporation','CP101'), (1002,'Rational Software Corporation','CP102'),
 (1003,'Oracle','CP103'),(1004,'DataRaker','CP104'), (1005,'Ksplice','CP105'),
 (1006,'Microsoft','CP106'),(1007,'Hotmail','CP107'), (1008,'Flash Communications','CP108')                   

Create table CompanyGroup(IdCompanyGroup int identity(1,1) primary key,IdParentCompany int,IdchildCompany int,
FOREIGN KEY (IdParentCompany) REFERENCES Company(IdCompany)
        ,FOREIGN KEY (IdchildCompany) REFERENCES Company(IdCompany)
)

Insert into CompanyGroup values(1000,1001),(1000,1002),
(1003,1004),(1003,1005),
(1006,1007),(1006,1006)

The logic of this table would be

Informix Corporation,Rational Software Corporation was aquired by IBM so IBM is parent and others are child in the CompanyGroup table.
DataRaker, Ksplice was aquired by Oracle, so Oracle is parent and others are child in the CompanyGroup table.
deto the microsoft example.

I need to loop the company table based on the idcompany and map it with CompanyGroup Table. If the IdCompany exists in the
Group table's IdchildCompany column then get the corresponding IdParentCompany and get the companycode of it.

Exprected Result:

Any Sample Query please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-11 : 21:04:40

i didn't see 1008 in CompanyGroup table

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-11 : 22:01:56

Sorry small data mistake. The last value will be
CompanyGroup should be (1006,1008). Any help please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-11 : 23:16:36

your data is not consistent.

You have (1006,1006) but not (1000,1000) and (1003,1003) ?

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-12 : 09:51:03

Hi Khtan,

Thanks for the reply.

Here is the data:

Insert into CompanyGroup values(1000,1001),(1000,1002),
(1003,1004),(1003,1005),
(1006,1007),(1006,1008)

This is how i achieved.

select
    c.IdCompany,
    c.CompanyName,
    isnull(oa.CompanyCode, c.CompanyCode) CompanyCode
from
    dbo.Company c
    outer apply (select
                     c1.CompanyCode
                 from
                     dbo.Company c1
                     inner join dbo.CompanyGroup cg
                         on c1.IdCompany = cg.IdParentCompany
                 where
                     c.IdCompany = cg.IdchildCompany)oa(CompanyCode)
order by
    c.IdCompany;

Any comments or suggestions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources