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
LVL 2
jshesekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
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

0
MlandaTCommented:
--parent companies treated seperately
SELECT co_ID ParentID, [Company Name] ParentName, null ChildID, null ChildName
FROm Companies
WHERE NOT EXISTS (SELECT * FROM KeyTable WHERE CHildID = COmpanies.co_ID)

UNION 

SELECT 
  ParentC.co_ID, ParentC.[Company Name],
  ChildC.co_ID, ChildC.[Company Name]
FROM KeyTable
  INNER JOIN Companies ParentC ON ParentC.co_ID = KeyTable.ParentID
  INNER JOIN Companies ChildC ON ChildC.co_ID = KeyTable.ChildID

ORDER BY ChildID

Open in new window

Table creation
CREATE TABLE Companies
    (co_ID int, [Company Name] varchar(27))
;
    
INSERT INTO Companies
    (co_ID, [Company Name])
VALUES
    (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')
;


CREATE TABLE KeyTable
    (kid int, parentID int, ChildID int)
;
    
INSERT INTO KeyTable
    (kid, parentID, ChildID)
VALUES
    (1, 1, 4),
    (2, 1, 5),
    (3, 1, 6),
    (4, 2, 7),
    (5, 2, 8),
    (6, 3, 9),
    (7, 3, 10),
    (8, 3, 11)
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jshesekAuthor Commented:
This worked.  Thanks for both suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.