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?
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;