CODEWITHSUNDEEP
sqldatabaseoracle-database
bn60
bn60

Reputation: 139

Oracle - Query address from multiple Column

I am trying to query a Staff's addresses (Home and Correspondence Address).

What I have for Staff table is

Staff_ID, First_Name, Last_Name, Home_Adress, Corr_Address

And for Address table is

Address_ID, Line1, Line2, Line3, Post_Code

Both the addresses are linked to the Address_ID. I've read around the site, it seems the approach for this is Union or Join, which I'm not really sure how to write it down. 

SELECT First_Name, Last_Name, Home_Adress, Corr_Address FROM STAFF
UNION
SELECT Address_ID FROM ADDRESS
GROUP BY First_Name

Some help would be appreciated, thanks!

Upvotes: 0

Views: 505

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You need to JOIN the table Address two times, one to get the HomeAddress and another one to get the Corr_Address:

SELECT
  s.First_Name,
  s.Last_Name,
  ah.Line1     AS HomeLine1,
  ah.Line2     AS HomeLine2,
  ah.Line3     AS HomeLine3,
  ah.Post_Code AS HomePost_Code,
  ac.Line1     AS CorrLine1,
  ac.Line2     AS CorrLine2,
  ac.Line3     AS CorrLine3,
  ac.Post_Code AS corrPostCode
FROM Staff AS s
INNER JOIN address AS ah ON s.Home_Address = ah.address_ID
INNER JOIN address AS ac ON s.corr_address = ac.addressid;

Update:

If you want to combine the addresses' details for each address in one field, you can do this:

SELECT
  s."First_Name",
  s."Last_Name",
  ah."Line1" || ', ' || ah."Line2" || ', ' || ah."Line3" || ', ' || ah."Post_Code" AS HomeAddress,
  ac."Line1" || ', ' || ac."Line2" || ', ' || ac."Line3" || ', ' || ac."Post_Code" AS CorrAddress
FROM Staff s
INNER JOIN address ah ON s."Home_Adress" = ah."Address_ID"
INNER JOIN address ac ON s."Corr_Address" = ac."Address_ID";

See it in action here:

This will give you something like this:

| FIRST_NAME | LAST_NAME |                   HOMEADDRESS |                   CORRADDRESS |
------------------------------------------------------------------------------------------
|        foo |       bar | line11, line12, line13, 34222 |   line12, line22, line32, 653 |
|       foo2 |      bar2 | line13, line23, line33, 34545 | line14, line25, line35, 33452 |

Upvotes: 2

Related Questions