Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Help with Corelated sub-query

pshah2kNov 6 2007 — edited Nov 7 2007
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
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.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 5 2007
Added on Nov 6 2007
5 comments
7,091 views