Skip to content
Advertisement

How to get address by user’s id oracle

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":"tets1@.com"
         }
      }
   }
}

So I plan to going with this table structure,

USER_TABLE

+------------+-------+-------+------------+--------+
| USERNAME   | FNAME | LNAME | EMAIL      | MOBILE |
+------------+-------+-------+------------+--------+
| user_00002 | Jhone | sean  | tets1@.com | 32323  |
+------------+-------+-------+------------+--------+
| user_00003 | Kevin | Niga  | tets2@.com | 23232  |
+------------+-------+-------+------------+--------+
| user_00005 | Mal   | Ruvaw | tets3@.com | 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?

enter image description here

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement