Hi,
I need to display the data for a applicant with a single record by joining the below 2 tables with applicant id and all the residence and offfice address to be displayed in the same row.
I have trIed using subquery to fetch the address with address_type but it seems like i need to repeat the same query with different column names to get the result...
Please guide how to achieve this.
Expected columns
I_APPLICANT_ID SZ_APPLICANT_NAME RES_ADDRESS_1 RES_ADDRESS_2 RES_CITY RES_STATE RES_ZIPCODE OFC_ADDRESS_1 OFC_ADDRESS_2 OFC_CITY OFC_STATE OFC_ZIPCODE
SAMPLE DATE
CREATE TABLE TEST_APPLICANT
(I_APPLICANT_ID NUMBER(10) not null,
SZ_APPLICANT_NAME VARCHAR2(60)
);
INSERT INTO TEST_APPLICANT VALUES (1, 'JOHN');
INSERT INTO TEST_APPLICANT VALUES (2, 'RAM');
INSERT INTO TEST_APPLICANT VALUES (3, 'SALMAN');
create table TEST_ADDRESS_DETAILS
(
I_APPLICANT_ID NUMBER(10) not null,
SZ_ADDRESS_TYPE VARCHAR2(5), --O - OFFICE, R - RESIDENCE
sz_address_1 VARCHAR2(60),
sz_address_2 VARCHAR2(60),
sz_city VARCHAR2(60),
sz_state VARCHAR2(60),
sz_postal_code VARCHAR2(15)
);
INSERT INTO TEST_ADDRESS_DETAILS VALUES (1, 'R', '3rd street','Kotvakam','Chennai','Tamil Nadu','1212');
INSERT INTO TEST_ADDRESS_DETAILS VALUES (1, 'O', '4th street','Kotvakam','Chennai','Tamil Nadu','1212');
INSERT INTO TEST_ADDRESS_DETAILS VALUES (2, 'R', '5th street','Kotvakam','Chennai','Tamil Nadu','1212');
INSERT INTO TEST_ADDRESS_DETAILS VALUES (2, 'O', '6th street','Kotvakam','Chennai','Tamil Nadu','1212');
INSERT INTO TEST_ADDRESS_DETAILS VALUES (3, 'R', '7th street','Kotvakam','Chennai','Tamil Nadu','1212');
INSERT INTO TEST_ADDRESS_DETAILS VALUES (3, 'O', '8th street','Kotvakam','Chennai','Tamil Nadu','1212');