2 Replies Latest reply: Apr 11, 2013 3:44 AM by Friend RSS

    using OR condition in multiple columns subquery  SQL

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

      l

      Edited by: Friend on Apr 11, 2013 1:21 AM

      Edited by: Friend on Apr 11, 2013 1:22 AM