Skip to Main Content

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!

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,096 views