8 Replies Latest reply: Feb 28, 2014 11:32 AM by Partha Sarathy S RSS

    Select data

    917874

      I have 2 tables like below

      T1

      col1    col2  col3

      111   ABCD1
      222   DEFD2
      333   IJKD3

       

       

      T2

      col1 col2 col3 col4 col5

      1 111 ABC D1  A1

      2 111 ABC E2  A2

      3 222 DEF D2  A1

      4 333 IJK F3  A1

       

      I want the records from T1 where the col3 is not matching with col4 of T2 table.

       

      The output is

      333  IJK  D3

        • 1. Re: Select data
          Roger

          So you mean T1.col1 should match T2.col2 and T1.col2 should match T2.col3 but T1.col3 should not match T2.col4

           

          so...

           

          SELECT T1.*

          FROM T1, T2

          WHERE T1.col1 = T2.col2

          AND T1.col2 = T2.col3

          AND T1.col3 <> T2.col4

          /

           

          Question: may one of those columns have null values?

           

          hth

          • 2. Re: Select data
            Hoek

            select *

            from   t1

            where not exists ( select null

                               from   t2

                               where  t2.col4 = t1.col3

                             );

            • 3. Re: Select data
              Frank Kulash

              Hi,

               

              If the combination (col1, col2, col3) is unique in t1, then you can also use MINUS, like this:

              SELECT  col1, col2, col3

              FROM    t1

                  MINUS

              SELECT  col2, col3, col4

              FROM    t2

              ;

              If that combination is not unique, you can still use MINUS, but the output will be unique.

              • 4. Re: Select data
                Frank Kulash

                Hi,

                 

                If none of the columns involved can be NULL, then a NOT IN subquery is another option:

                SELECT  col1, col2, col3

                FROM    t1

                WHERE   (col1, col2, col3)  NOT IN (

                                                       SELECT  col2, col3, col4

                                                       FROM    t2

                                                   )

                ;

                and semi-join is yet another option:

                SELECT  t1.col1, t1.col2, t1.col3

                FROM               t1

                LEFT OUTER JOIN    t2   ON   t1.col1  = t2.col1

                                        AND  t1.col2  = t2.col2

                                        AND  t1.col3  = t3.col3

                WHERE   t2.col1  IS NULL

                ;

                • 5. Re: Select data
                  917874

                  Note that the T2 has a 2 entries for the 111 which is not required for the output, because one of the value D1 is matching record is there in T2.

                  So the only entry which is valid is 333 as output.

                   

                  T1 Table Col3 is having sometime nulls also.

                   

                  With all the above 3 queries I am not getting the desired result.

                  • 6. Re: Select data
                    Hoek

                    Then post a decent testcase/dataset.

                    Use CREATE TABLE and INSERT INTO statements.

                    Re: 2. How do I ask a question on the forums?

                    • 7. Re: Select data
                      Frank Kulash

                      Hi,

                       

                       

                      917874 wrote:

                       

                      ... T1 Table Col3 is having sometime nulls also.

                       

                      With all the above 3 queries I am not getting the desired result.

                      When you do post the sample data (CREATE TABLE and INSERT INTO statements) and desired results, make sure they include examples of NULLs in places where they are possible.

                       

                      I see 5 queries above in this thread.  Are you saying 2 of them do give the desired results?

                      • 8. Re: Select data
                        Partha Sarathy S

                        How about this?

                         

                        SELECT COL1,

                               COL2,

                               COL3

                        FROM T1

                        WHERE NOT EXISTS (SELECT 1 FROM

                                           T2 WHERE T1.COL3=T2.COL4)

                        AND T1.COL3 IS NOT NULL;

                        Example using sample data provided.

                         

                        WITH T1(COL1,COL2,COL3)AS (

                        SELECT 111,'ABC','D1' FROM DUAL UNION ALL

                        SELECT 222,'DEF','D2' FROM DUAL UNION ALL

                        SELECT 333,'IJK','D3' FROM DUAL),

                        T2(COL1,COL2,COL3,COL4,COL5) AS (

                        SELECT 1,111,'ABC','D1','A1' FROM DUAL UNION ALL

                        SELECT 2,111,'ABC','E2','A2' FROM DUAL UNION ALL

                        SELECT 3,222,'DEF','D2','A1' FROM DUAL UNION ALL

                        SELECT 4,333,'IJK','F3','A1' FROM DUAL)

                        SELECT COL1,

                               COL2,

                               COL3

                        FROM T1

                        WHERE NOT EXISTS (SELECT 1 FROM

                                           T2 WHERE T1.COL3=T2.COL4)

                        AND T1.COL3 IS NOT NULL;

                         

                        OUTPUT;

                        333 IJK D3