Hi Team,
I need the this requirement to make the manual analysis between two tables in a easier way. These tables do not have any direct relation.
Please find below scripts to create table, insert data along with incorrect select query I have,
CREATE TABLE TEST1 (
KEY_PRM VARCHAR(30),
LIST_NAME VARCHAR(30),
TEL_NO VARCHAR(10),
CREATE_DT DATE,
CEASE_DT DATE,
STATUS VARCHAR(20) --VALUE USED EITHER A OR C
);
CREATE TABLE TEST2(
KEY_PRM1 VARCHAR(30),
TEL_NO VARCHAR(20),
TRANS_DATE DATE,
TRANS_TYPE VARCHAR(20)/*VALUE USED ARE INSERT,CEASE(Many others)*/
);
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('1','SAM','0123456789','01-APR-2013',NULL,'A');
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('2','MARS','0123456789','10-APR-2013','12-APR-2013','C');
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('3','PLUTO','0123456799','05-APR-2013',NULL,'A');
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('4','MAN','0123456999','06-APR-2013',NULL,'A');
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('6','JIN','0123456999','06-APR-2013','08-APR-2013','C');
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('7','RIM','0123456789','20-APR-2013',NULL,'A');
INSERT INTO TEST1 (KEY_PRM,LIST_NAME,TEL_NO,CREATE_DT,CEASE_DT,STATUS) VALUES('8','JIM','0123456789','12-APR-2013','12-APR-2013','C');
COMMIT;
INSERT INTO TEST2 (KEY_PRM1,TEL_NO,TRANS_DATE,TRANS_TYPE) VALUES('10','0123456789','01-APR-2013','INSERT');
INSERT INTO TEST2 (KEY_PRM1,TEL_NO,TRANS_DATE,TRANS_TYPE) VALUES('11','0123456789','12-APR-2013','CEASE');
INSERT INTO TEST2 (KEY_PRM1,TEL_NO,TRANS_DATE,TRANS_TYPE) VALUES('12','0123456799','12-APR-2013','INSERT');
INSERT INTO TEST2 (KEY_PRM1,TEL_NO,TRANS_DATE,TRANS_TYPE) VALUES('13','0123456999','06-APR-2013','INSERT');
COMMIT;
SELECT T1.KEY_PRM,T1.STATUS,T1.CREATE_DT,T1.CEASE_DT FROM TEST1 T1 WHERE
(T1.TEL_NO,(T1.CREATE_DT OR T1.CEASE_DT)) IN (SELECT T2.TEL_NO,T2.TRANS_DATE from TEST2 T2 where TRANS_TYPE in ('INSERT','CEASE');
/*RESULTS SHOULD PRODUCE T1.KEY_PRM = 1,2,4,6,8.*/
There are two tables TEST1 T1 & TEST2 T2 having only matching column as TEL_NO but with many duplicates in both tables.
Query results to produce T1.KEY_PRM from TEST1 T1 where both CONDITION 1 and CONDITION 2 match
CONDITION 1:- T1.TEL_NO MATCH WITH T2.TEL_NO
CONDITION 2:- EITHER T1.CREATE_DT OR T1.CEASE_DT MATCH WITH T2.TRANS_DATE.
Also kindly let me If it is not possible to create such select query?
Edited by: BluShadow on 11-Apr-2013 09:42
added {noformat}
{noformat} tags for readability. Please read {message:id=9360002} and learn to do this yourself in future.