Menu
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?
947
Merging tables by columns. Multiple tables can be merged by columns in SQL using joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other).
Method 1: Relational Algebra Relational algebra is the most common way of writing a query and also the most natural way to do so. The code is clean, easy to troubleshoot, and unsurprisingly, it is also the most efficient way to join two tables.
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
Ans: Joining two tables in SQL can be done in four major ways: Inner Join (returns rows with matching columns), Left Join (ALL records in the left table and matching records in the right table), Right Join (ALL records in the right table and matching records in the left table), and Union (removes duplicates).
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
