Forum Stats

  • 3,733,252 Users
  • 2,246,738 Discussions
  • 7,856,634 Comments

Discussions

Help with Corelated sub-query

pshah2k
pshah2k Member Posts: 172
edited November 2007 in SQL & PL/SQL
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

Comments

  • lmconsite
    lmconsite Member Posts: 29
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2007
    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.
    2067650

    I mentioned alike logic in this thread.
    2040085

    I recommend these articles.
    http://www.dbazine.com/ofinterest/oi-articles/celko5
    http://www.dbazine.com/ofinterest/oi-articles/celko18
  • lmconsite
    lmconsite Member Posts: 29
    Aketi,

    I stand corrected.

    Regards,

    Luis
  • pshah2k
    pshah2k Member Posts: 172
    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.
This discussion has been closed.