CODEWITHSUNDEEP
mysqlsql
mmaceachran
mmaceachran

Reputation: 3358

SQL outer join one table with 2 tables

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!

Upvotes: 1

Views: 59

Answers (3)

bksi
bksi

Reputation: 1625

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

Upvotes: 0

SqlZim
SqlZim

Reputation: 38063

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        |
+----------+------------+---------+-----------+----------+------------+

Upvotes: 4

trincot
trincot

Reputation: 351308

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

Upvotes: 1

Related Questions