Help with Corelated sub-query

pshah2k

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

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

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

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.
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

Aketi,

I stand corrected.

Regards,

Luis

pshah2k

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.