swirl84 swirl84 - 4 months ago 7
SQL Question

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

I have two tables,

USER and

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




Output should be,

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? Thanks in advance!

>

Answer

Try this:

SELECT u.*, uah.address as home_address, uaw.address as work_address
FROM users
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'