5 Replies Latest reply: Nov 7, 2007 9:55 AM by 27807 RSS

    Help with Corelated sub-query

    27807
      I am trying to improve this query? Is there a better way to write the query and get the same results. I hate the fact that I am querying a table 3 times.

      Select statement
      WITH t1 AS
           (SELECT 101 AS ID
                 , 1 AS a
                 , NULL AS b
                 , 5 AS c
              FROM DUAL
            UNION ALL
            SELECT 102
                 , NULL
                 , 2
                 , 5
              FROM DUAL
            UNION ALL
            SELECT 101
                 , 3
                 , 5
                 , 7
              FROM DUAL
            UNION ALL
            SELECT 101
                 , 1
                 , NULL
                 , NULL
              FROM DUAL)
         , t2 AS
           (SELECT 101 AS ID
                 , 1 AS x
              FROM DUAL
            UNION ALL
            SELECT 101
                 , 5
              FROM DUAL
            UNION ALL
            SELECT 102
                 , 5
              FROM DUAL
            UNION ALL
            SELECT 102
                 , 2
              FROM DUAL)
      SELECT *
        FROM t1
       WHERE t1.ID = 101
         AND (   t1.a IS NULL
              OR t1.a IN (SELECT t2.x
                            FROM t2
                           WHERE t2.ID = t1.ID))
         AND (   t1.b IS NULL
              OR t1.b IN (SELECT t2.x
                            FROM t2
                           WHERE t2.ID = t1.ID))
         AND (   t1.c IS NULL
              OR t1.c IN (SELECT t2.x
                            FROM t2
                           WHERE t2.ID = t1.ID))
      Output:
              ID          A          B          C
      ---------- ---------- ---------- ----------
             101          1                     5
             101          1                      
      -- Thanks
        • 1. Re: Help with Corelated sub-query
          lmconsite
          Please, try this:

          WITH t1 AS
          (SELECT 101 AS ID
          , 1 AS a
          , NULL AS b
          , 5 AS c
          FROM DUAL
          UNION ALL
          SELECT 102
          , NULL
          , 2
          , 5
          FROM DUAL
          UNION ALL
          SELECT 101
          , 3
          , 5
          , 7
          FROM DUAL
          UNION ALL
          SELECT 101
          , 1
          , NULL
          , NULL
          FROM DUAL)
          , t2 AS
          (SELECT 101 AS ID
          , 1 AS x
          FROM DUAL
          UNION ALL
          SELECT 101
          , 5
          FROM DUAL
          UNION ALL
          SELECT 102
          , 5
          FROM DUAL
          UNION ALL
          SELECT 102
          , 2
          FROM DUAL)
          SELECT *
          FROM t1
          WHERE t1.ID = 101
          AND EXISTS
          (SELECT *
          FROM t2
          WHERE t1.ID = t2.ID
          AND t1.a = t2.x OR t1.a IS NULL
          AND t1.b = t2.x OR t1.b IS NULL
          AND t1.c = t2.x OR t1.c IS NULL);

          Regards,

          Luis
          • 2. Re: Help with Corelated sub-query
            Aketi Jyuuzou
            Hi lmescher.
            Unfortunately, I think that your query is wrong.
            for instance
            WITH t1 AS
                 (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
                  SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
                  SELECT 101, 5,99,99 FROM DUAL UNION ALL
                  SELECT 101, 1, NULL, NULL FROM DUAL)
               , t2 AS
                 (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
                  SELECT 101 , 5 FROM DUAL UNION ALL
                  SELECT 101 , 3 FROM DUAL UNION ALL
                  SELECT 101 , 5 FROM DUAL UNION ALL
                  SELECT 101 , 7 FROM DUAL)
            SELECT *
            FROM t1
            WHERE t1.ID = 101
            AND EXISTS
            (SELECT *
            FROM t2
            WHERE t1.ID = t2.ID
            AND t1.ColA = t2.ColX OR t1.ColA IS NULL
            AND t1.ColB = t2.ColX OR t1.ColB IS NULL
            AND t1.ColC = t2.ColX OR t1.ColC IS NULL);
             ID  ColA  ColB  ColC
            ---  ----  ----  ----
            101     1  null     5
            101     3     5     7
            101     5    99    99
            101     1  null  null
            If you has mistake for combination of "and" and "or",
            Query becomes below.
            But ResultSet remains wrong ResultSet.
            WITH t1 AS
                 (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
                  SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
                  SELECT 101, 5,99,99 FROM DUAL UNION ALL
                  SELECT 101, 1, NULL, NULL FROM DUAL)
               , t2 AS
                 (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
                  SELECT 101 , 5 FROM DUAL UNION ALL
                  SELECT 101 , 3 FROM DUAL UNION ALL
                  SELECT 101 , 5 FROM DUAL UNION ALL
                  SELECT 101 , 7 FROM DUAL)
            SELECT *
            FROM t1
            WHERE t1.ID = 101
            AND EXISTS
            (SELECT *
            FROM t2
            WHERE t1.ID = t2.ID
              AND (t1.ColA = t2.ColX OR t1.ColA IS NULL)
              AND (t1.ColB = t2.ColX OR t1.ColB IS NULL)
              AND (t1.ColC = t2.ColX OR t1.ColC IS NULL));
             ID  ColA  ColB  ColC
            ---  ----  ----  ----
            101     1  null  null
            • 3. Re: Help with Corelated sub-query
              Aketi Jyuuzou
              This is an interesting question.
              WITH t1 AS
                   (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
                    SELECT 102, NULL, 2, 5 FROM DUAL UNION ALL
                    SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
                    SELECT 101, 5,99,99 FROM DUAL UNION ALL
                    SELECT 101, 1, NULL, NULL FROM DUAL)
                 , t2 AS
                   (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
                    SELECT 101 , 5 FROM DUAL UNION ALL
                    SELECT 102 , 5 FROM DUAL UNION ALL
                    SELECT 102 , 2 FROM DUAL UNION ALL
                    SELECT 101 , 3 FROM DUAL UNION ALL
                    SELECT 101 , 5 FROM DUAL UNION ALL
                    SELECT 101 , 7 FROM DUAL)
              select *
                from t1 a
              where ID = 101
                 and exists(select 1
                              from t2 b
                             where b.ID = a.ID
                            having (a.ColA is null or max(case when b.ColX = a.ColA then 1 else 0 end) = 1)
                               and (a.ColB is null or max(case when b.ColX = a.ColB then 1 else 0 end) = 1)
                               and (a.ColC is null or max(case when b.ColX = a.ColC then 1 else 0 end) = 1));
               ID  ColA  ColB  ColC
              ---  ----  ----  ----
              101     1  null     5
              101     3     5     7
              101     1  null  null
              Furthermore,
              We can use below alternative solution.
              Below alternative solution is used Boolean arithmetic (http://www.allaboutcircuits.com/vol_4/chpt_7/2.html)
              WITH t1 AS
                   (SELECT 101 AS ID, 1 AS ColA, NULL AS ColB, 5 AS ColC FROM DUAL UNION ALL
                    SELECT 102, NULL, 2, 5 FROM DUAL UNION ALL
                    SELECT 101, 3, 5, 7 FROM DUAL UNION ALL
                    SELECT 101, 5,99,99 FROM DUAL UNION ALL
                    SELECT 101, 1, NULL, NULL FROM DUAL)
                 , t2 AS
                   (SELECT 101 AS ID , 1 AS ColX FROM DUAL UNION ALL
                    SELECT 101 , 5 FROM DUAL UNION ALL
                    SELECT 102 , 5 FROM DUAL UNION ALL
                    SELECT 102 , 2 FROM DUAL UNION ALL
                    SELECT 101 , 3 FROM DUAL UNION ALL
                    SELECT 101 , 5 FROM DUAL UNION ALL
                    SELECT 101 , 7 FROM DUAL)
              select *
                from t1 a
              where ID = 101
                 and exists(select 1
                              from t2 b
                             where b.ID = a.ID
                            having max(case when b.ColX = a.ColA or a.ColA is null then 1 else 0 end)
                                 * max(case when b.ColX = a.ColB or a.ColB is null then 1 else 0 end)
                                 * max(case when b.ColX = a.ColC or a.ColC is null then 1 else 0 end) = 1);
              This thread is dealt logic which is "max(case when P(X) then 1 else 0 end) = 1" is for some X:P(X).
              And this thread deals alike question.
              Re: Select only records with same Id

              I mentioned alike logic in this thread.
              Re: Newbie: Simple select with 'IN ALL'

              I recommend these articles.
              http://www.dbazine.com/ofinterest/oi-articles/celko5
              http://www.dbazine.com/ofinterest/oi-articles/celko18
              • 4. Re: Help with Corelated sub-query
                lmconsite
                Aketi,

                I stand corrected.

                Regards,

                Luis
                • 5. Re: Help with Corelated sub-query
                  27807
                  Thanks a lot for your solution. It works like a charm. I can now add one more thing that I should try to leran.

                  I also want to thank others that responded.