Joining two tables, multiple rows into a single row different columns

Question

I have two tables.

USER

USER_ID | USER_NAME
--------------------
659    |  John  
660    |  Andrew 
661    |  Bianca
--------------------

USER_ADDRESS

USER_ID |TYPE |    ADDRESS
------------------------------
659     | HOME |    New York
659     | WORK |    New Jersey
660     | HOME |    San Francisco
660     | WORK |    Fremont
------------------------------

I want to join multiple records from the 2nd table into a single row on the 1st table.

USER_ID | USER_NAME | HOME_ADDRESS | WORK_ADDRESS 
--------------------------------------------------
659   |  John    |   New York      | New Jersey
660   |  Andrew  |   San Francisco | Fremont

How do I get the above output in a select query?

score 4 · Accepted Answer · edited May 22 '18 at 07:56

4

Try this:

SELECT u.*, uah.address as home_address, uaw.address as work_address
FROM users u
LEFT OUTER JOIN user_address uah
    ON u.user_id = uah.user_id
    AND uah.type = 'HOME'
LEFT OUTER JOIN user_address uaw
    ON u.user_id = uaw.user_id
    AND uaw.type = 'WORK'

edited May 22 '18 at 07:56

jonasfh

4,151
2
21
37

answered Jul 27 '16 at 16:11

Joe Taras

15,166
7
42
55

What is the purpose of doing a LEFT OUTER JOIN? Is it just in case they don't have any information stored in the USER_ADDRESS table but still want them to be displayed? – CodyMR Jul 27 '16 at 16:18

Joining two tables, multiple rows into a single row different columns

1 Answers1