join to different columns in SQL Server

Question

I have a main table below Table1:

Name                ID      Entry_Dt
PEREZ               2000        8/14/2014 
PEREZ               2000        8/29/2017 
Domingo             2098        8/29/2017

I have another table2 below:

kid_id  Parent_id   
2098    2000

I would like my result like this:

Name     Kid_id     Parent_id   Entry_dt
PEREZ               2000        8/14/2014
PEREZ               2000        8/29/2017
Domingo   2098                  8/29/2017

I have used two different methods like example below but the result is not desirable.

1: union method

select *
FROM Table1 A INNER JOIN Table2 b
ON A.ID= B.Kid_id
union
select *
FROM Table1 A INNER JOIN Table2 b
ON A.ID= B.Parent_id

2: left join method:

select *
FROM Table1 A LEFT JOIN Table2 b
ON (A.ID= B.Kid_id or A.ID = B.Parent_id)

Why are my methods not working? Any ideas for improving?

Try with full outer join. Read it post: https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — Alvaro Gili, Apr 09 '19 at 15:04
Please explain the logic. I don't understand why `id` is missing from the third row. Nor do I understand why `Name` is included in both tables, particularly twice in the second table. — Gordon Linoff, Apr 09 '19 at 15:10

Joe Taras · Answer 1 · 2019-04-09T15:12:03.147

1

You must link two distinct JOINs, the first is a LEFT JOIN for the parent (if exists) and the second is a LEFT JOIN too for a kid (if exists).

So you expose in the field list the kind_id / parent_id as two different column

Try this:

SELECT main.name, main.id, kid.Kid_id, parent.parent_id, main.Entry_dt
FROM Table1 main
LEFT JOIN Table2 kid
ON main.id = kid.Kid_id
LEFT JOIN Table2 parent
ON main.id = parent.Parent_id

See the SQL Fiddle

edited Apr 09 '19 at 15:12

answered Apr 09 '19 at 15:04

Joe Taras

15,166
7
42
55

very close to where I would have done. Future suggestion, make your aliases reflect the context... such as "A", "B" and "C" as Main, Parent, Child. Context vs just abc easier to follow context of the alias. I know, this is an example, but do as if you needed to know proper purpose for future too. – DRapp Apr 09 '19 at 15:10
@Drapp: Yes, I've used the same alias (except C) of our OP. You're right, are cryptic :) – Joe Taras Apr 09 '19 at 15:12
@Drapp: I've changed the alias ;) – Joe Taras Apr 09 '19 at 15:14

score 0 · Answer 2 · answered Apr 09 '19 at 15:04

0

You should use left join

select A.Name, A.ID,  B.kid_id, B.Entry_dt
FROM Table1 A 
LEFT JOIN Table2 B   ON A.ID= B.Kid_id

answered Apr 09 '19 at 15:04

ScaisEdge

131,976
10
91
107

FizzBuzz · Answer 3 · 2019-04-09T15:44:59.567

You don't need a LEFT JOIN here - you can do an INNER JOIN too. The point of a LEFT JOIN is when you need rows in one table that don't exist in the other, but here, each row in Table1 has a match in Table2. You need to use an OR in the join condition.

To get the output you'd like, you need to do some work in your select. See sample:

SELECT A.Name,
       CASE
           WHEN A.ID = B.Parent_Id THEN
               A.ID
           ELSE
               ''
       END AS ID,
       CASE
           WHEN A.ID = b.Kid_Id THEN
               b.Kid_Id
           ELSE
               ''
       END AS Kid_Id,
       CASE
           WHEN A.ID = b.Parent_Id THEN
               b.Parent_ID
           ELSE
               ''
       END AS Parent_Id,
       A.Entry_Dt
FROM Table1 A
    INNER JOIN Table2 b
        ON (
               A.ID = b.Kid_id
               OR A.ID = b.Parent_id
           );

SQL Fiddle Sample

score 0 · Answer 4 · answered Apr 09 '19 at 15:22

You can achieve this by joining table2 twice with table1. one as parent and another as kid.

Query

select table1.name, table1.ID, parent.parent_Id, kid.kid_id,table1.Entry_dt
from table1
left join table2 parent on parent.parent_Id = table1.id
left join table2 kid on kid.kid_id = table1.id

Output

join to different columns in SQL Server

1: union method

2: left join method:

4 Answers4