SQL outer join one table with 2 tables

Question

I need some SQL help. I am a Java guy by trade and I really don't know even how to ask this question. I have 3 tables, Call them Person, Children, Friends. Person is an ID and a Name:

|   id   |    name    |
---------------------
|    1   |    Joe     |

Let say Children is the same but with a FK back to person

|   id   |    personId    |   name   |
-------------------------------------
|   1    |       1        |   Frank  |
|   2    |       1        |   Dan    |

and friends are the same thing

|   id   |    personId    |   name   |
-------------------------------------
|   1    |       1        |   Will   |
|   2    |       1        |   Bob    |

Obviously this is a simplified version of my real problem, but the structure is the same. I need to pull all of this data in one SQL pull such that I get this back

 | personId  | personName  |  childId  | childName |  friendId  |  friendName
------------------------------------------------------------------------------------------
 |     1     |    Joe      |     1     |   Frank   |   null     |    null
 |     1     |    Joe      |     1     |   Dan     |   null     |    null
 |     1     |    Joe      |    null   |   null    |     1      |    Will
 |     1     |    Joe      |    null   |   null    |     2      |    Bob

I have tried multiple join techniques, but can’t seem to crack it. SQL was never my best subject. Now I am parsing this into a Java object person with List<> of friends and children so obviously this will work too:

 | personId  | personName  |  childId  | childName |  friendId  |  friendName
------------------------------------------------------------------------------------------
 |     1     |    Joe      |    null   |   null    |   null     |    null
 |   null    |    null     |     1     |   Frank   |   null     |    null
 |   null    |    null     |     1     |   Dan     |   null     |    null
 |   null    |    null     |    null   |   null    |     1      |    Will
 |   null    |    null     |    null   |   null    |     2      |    Bob

Anything that will allow a clean for loop in my code to build this.

Thanks!

I'm no expert but two full outer joins on person.id = children.personId and person.id = friends.personId should work. What have you tried and what results were you getting? — mreff555
– mreff555, Commented Feb 24, 2017 at 21:48

SqlZim · Accepted Answer · 2017-02-24 22:00:52Z

4

select 
     p.id    as personId
  ,  p.name  as personName
  ,  c.id    as childId
  ,  c.name  as childName
  ,  null    as friendId
  ,  null    as friendName
from person p
  inner join child c
    on p.id = c.personId
union all 
select 
     p.id    as personId
  ,  p.name  as personName
  ,  null    as childId
  ,  null    as childName
  ,  f.id    as friendId
  ,  f.name  as friendName
from person p
  inner join friend f
    on p.id = f.personId;

rextester: http://rextester.com/BSPEC33394

returns:

+----------+------------+---------+-----------+----------+------------+
| personId | personName | childId | childName | friendId | friendName |
+----------+------------+---------+-----------+----------+------------+
|        1 | joe        | 1       | frank     | NULL     | NULL       |
|        1 | joe        | 2       | dan       | NULL     | NULL       |
|        1 | joe        | NULL    | NULL      | 1        | will       |
|        1 | joe        | NULL    | NULL      | 2        | bob        |
+----------+------------+---------+-----------+----------+------------+

edited Feb 24, 2017 at 22:00

answered Feb 24, 2017 at 21:55

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bksi Over a year ago

Are you sure inner join would return nulls? I think you should use left outer join instead

SqlZim Over a year ago

@bksi click the rextester link and see check it out. rextester.com/BSPEC33394

trincot · Accepted Answer · 2017-02-24 21:49:59Z

You could outer join with the union of children and friends, and then check which of both you are matching with to determine what to output in each column (using case when):

select    person.id,
          person.name,
          case when rel.kind = 1 then rel.id   end as childId,
          case when rel.kind = 1 then rel.name end as childName,
          case when rel.kind = 2 then rel.id   end as friendId,
          case when rel.kind = 2 then rel.name end as friendName
from      person
left join (
           select id, personId, name, 1 as kind
           from   children
           union all
           select id, personId, name, 2 as kind
           from   friends
          ) as rel
       on rel.personId = person.id
order by  person.id,
          rel.kind
          rel.id

bksi · Accepted Answer · 2017-02-26 01:12:03Z

0

You can use LEFT JOIN to acheive this:

SELECT 
     p.id    as personId
  ,  p.name  as personName
  ,  c.id    as childId
  ,  c.name  as childName
  ,  f.id    as friendId
  ,  f.name    as friendName  
FROM person p
LEFT OUTER JOIN child c ON p.id = c.personId
LEFT OUTER JOIN friend f ON p.id = f.personId;

More info for left joins you can read here: https://www.w3schools.com/sql/sql_join_left.asp

answered Feb 26, 2017 at 1:12

bksi

1,6251 gold badge24 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL outer join one table with 2 tables

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related