asked on

TSQL Query Primary Table to a Key Table Joined Back to Primary Table

Hello,

I have a table of keys IDs and a table of Companies with those key IDs.
A company can own many sub-companies in the same table. The key table connects the parent and sub company together.

What query would I used to get something like this:
SELECT co_ID as P_ID, CompanyName as Parent, co_ID as C_ID, CompanyName as Child

The result would look like this:

ParentCompany 1, 1, Null
ParentCompany 2, 2, Null
ParentCompany 3, 3, Null
ParentCompany 1, 1, ChildCompany1ParentCompany1, 4
ParentCompany 1, 1, ChildCompany2ParentCompany1, 5
ParentCompany 1, 1, ChildCompany3ParentCompany1, 6
ParentCompany 2, 2, ChildCompany1ParentCompany2, 7
ParentCompany 2, 2, ChildCompany2ParentCompany2, 8
ParentCompany 3, 3, ChildCompany1ParentCompany3, 9
ParentCompany 3, 3, ChildCompany2ParentCompany3, 10
ParentCompany 3, 3, ChildCompany3ParentCompany3, 11

Companies
co_ID      Company Name
1        ParentCompany 1
2        ParentCompany 2
3        ParentCompany 3
4        ChildCompany1ParentCompany1
5        ChildCompany2ParentCompany1
6        ChildCompany3ParentCompany1
7        ChildCompany1ParentCompany2
8        ChildCompany2ParentCompany2
9        ChildCompany1ParentCompany3
10        ChildCompany2ParentCompany3
11        ChildCompany3ParentCompany3

Key table
kid      parentID      ChildID
1      1        4
2      1        5
3      1        6
4      2        7
5      2        8
6      3        9
7      3        10
8      3        11

JS

Éric Moreau

maybe something like this:

SELECT DISTINCT P.co_ID as P_ID, P.CompanyName as Parent, null as C_ID, null as Child
from [Key] as K
inner join Companies as P
on P.co_ID = K.parentID

union all 

SELECT P.co_ID as P_ID, P.CompanyName as Parent, C.co_ID as C_ID, C.CompanyName as Child
from [Key] as K
inner join Companies as P
on P.co_ID = K.parentID
inner join Companies as C
on C.co_ID = K.ChildID

Open in new window

ASKER CERTIFIED SOLUTION

Mlanda T

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JS List

ASKER

This worked. Thanks for both suggestions.