How to get address by user's id oracle-CodePudding

In my project, front end needs, following JSON data with this format. So I Created two table for user and their addresses,

{
   "data":{
      "user":{
         "cif":"102345678",
         "username":"user_00002",
         "fname":"Kevin",
         "lname":"",
         "contactDetails":{
            "permanentAddress":{
               "Line1":"no 1",
               "Line2":"cross lane",
               "city":"dilhi"
            },
            "correspondenceAddress":{
               "Line1":"no 2",
               "Line2":"main street",
               "city":"dilhi"
            },
            "mobile":"32323",
            "email":"[email protected]"
         }
      }
   }
}

So I plan to going with this table structure,

USER_TABLE

 ------------ ------- ------- ------------ -------- 
| USERNAME   | FNAME | LNAME | EMAIL      | MOBILE |
 ------------ ------- ------- ------------ -------- 
| user_00002 | Jhone | sean  | [email protected] | 32323  |
 ------------ ------- ------- ------------ -------- 
| user_00003 | Kevin | Niga  | [email protected] | 23232  |
 ------------ ------- ------- ------------ -------- 
| user_00005 | Mal   | Ruvaw | [email protected] | 34343  |
 ------------ ------- ------- ------------ --------

USER_ADDRESS

 ------------ ---------------- ------- ------------- ---------- 
| USERNAME   | ADDRESS_TYPE   | LINE1 | LINE2       | CITY     |
 ------------ ---------------- ------- ------------- ---------- 
| user_00002 | PERMANENT      | no 1  | cross lane  | dilhi    |
 ------------ ---------------- ------- ------------- ---------- 
| user_00002 | CORRESPONDENSE | no 2  | main street | dilhi    |
 ------------ ---------------- ------- ------------- ---------- 
| user_00003 | PERMANENT      | no 33 | cross lane  | jakartha |
 ------------ ---------------- ------- ------------- ---------- 
| user_00003 | CORRESPONDENSE | no 35 | main street | jakartha |
 ------------ ---------------- ------- ------------- ----------

Address table I created this way because, each user have two addresses, one is permanent and other one is correspondence. So is this good table structure to handle this? if yes, then I need to get following output from sql query, How can I do it?

CodePudding user response：

As per my experience, Your table structure seems correct to me. You can try pivoting the address table first and then join the table with user_table -

SELECT *
  FROM USER_TABLE U
  JOIN (SELECT USERNAME,
               MAX(CASE WHEN ADDRESS_TYPE = 'PERMANENT' THEN LINE1 ELSE NULL END) PR_LINE1,
               MAX(CASE WHEN ADDRESS_TYPE = 'PERMANENT' THEN LINE2 ELSE NULL END) PR_LINE2,
               MAX(CASE WHEN ADDRESS_TYPE = 'PERMANENT' THEN CITY ELSE NULL END) PR_CITY,
               MAX(CASE WHEN ADDRESS_TYPE = 'CORRESPONDENSE' THEN LINE1 ELSE NULL END) COR_LINE1,
               MAX(CASE WHEN ADDRESS_TYPE = 'CORRESPONDENSE' THEN LINE2 ELSE NULL END) COR_LINE2,
               MAX(CASE WHEN ADDRESS_TYPE = 'CORRESPONDENSE' THEN CITY ELSE NULL END) COR_CITY
          FROM USER_ADDRESS
         GROUP BY USERNAME) AD ON U.USERNAME = AD.USERNAME;