Multiple (two) Exists returns no rows 4

[RustyAfro]

For some reason the below query returns no rows when I use EXISTS and EXISTS.

SELECT columns
FROM tables
WHERE 
(
EXISTS ( subquery1 )
AND
EXISTS ( subquery1 )
)

But this query does using OR:

SELECT columns
FROM tables
WHERE 
(
EXISTS ( subquery1 )
OR
EXISTS ( subquery1 )
)

However, I truly only need rows that exists evaluates to TRUE in BOTH subqueries. Is there anyway to do this?

 

[RustyAfro]

Ack, the second subquery should say "subquery2

 

[SantaMufasa]

I truly only need rows that exists evaluates to TRUE in BOTH subqueries

This might be like saying "I truly only need lottery tickets that are winners in two lotteries, no just one or the other.<grin>


As an absolute fact of life in Oracle (and any other database environment) if you want rows to appear that are not appearing, then you must loosen your WHERE criteria. That is precisely why you get rows with the "OR" but not with the "AND"...ORs are less restrictive than ANDs.

So, if you want matching rows to "AND" from both subqueries, then you must loosen your WHERE criteria in one or the other or both queries...That's a fact, and that is the nature of the "Logic Beast".

Let us know if we can offer more help.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

hmm, I understand what you are saying. I thought perhaps my statement was trying to make the same row equal to two different things at the same time. But i'm not that familiar with EXISTS and was hoping it would work...

Below is a table with a sample representation of my data. I need to return unique ID's when the Unique ID has BOTH product "a" and product "b" (Hence the EXISTS with an "AND"). The query should only return Unique ID 1111 because it has both a and b. 2222 has product "a", but not "b" so it should not be returned.

I was hoping to do a correlated subquery as I understand them to have a performance benefit over uncorrelated, but I'm open to suggestions. Could you let me know the best method to do this? I would greatly appreciate it

tblSample

Unique ID   Product
---------   -------
1111        a
1111        b
1111        c
2222        a
2222        c

 

[RustyAfro]

Oh, and I should mention, using Exists with OR, does in fact bring back say 1111 with a row for A and a row for B. The AND is what kills it though and returns no rows.

 

[SantaMufasa]

Ahhh, yes, Rusty, I can see where you are going now. (Isn't it amazing how much better life is with an example?)

Section 1 -- Sample data:

SQL> select * from tblsample;

  UNIQUEID PRODU
---------- -----
      1111 a
      1111 b
      1111 c
      2222 a
      2222 c

5 rows selected.

Section 2 -- Query and results:

select distinct uniqueid from tblsample a
 where exists (select 'rusty' from tblsample
                where a.uniqueid = uniqueid
                  and product = 'a')
   and exists (select 'afro' from tblsample
                where a.uniqueid = uniqueid
                  and product = 'b');

  UNIQUEID
----------
      1111

1 row selected.

The EXISTS operator is extremely powerful and speedy, but the hard part, as you mentioned, is understanding how it works:

a TRUE response to the question, "EXISTS", results from its associated query returning any NOT NULL value from the associated SELECT sub-query. In this case, I chose 'rusty' and 'afro' to be the arbitrary NOT NULL value that results from the respective queries if even one row matches the subquery's WHERE clause (which correlates with the current row's "UniqueID" column in the outer query.

Let us know if this all makes sense to you, or if you have questions. Regardless, the query works and does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

I know I should be getting this, but I still can't get the query to work. Below is the real query in question. If I put an OR between the two exists (using an extra "( )"), I get the two "A" and "B" rows, but the AND returns none.

SELECT
   PLH300_PH_DIMSN.PH_NBR
  ,PLH300_PH_DIMSN.PH_CAT_RMC_CD
  ,SUM(( MBR304_TM_PH_SUMM.ELG_CN ))
FROM
   PLH300_PH_DIMSN
  ,MBR304_TM_PH_SUMM
WHERE
         PLH300_PH_DIMSN.PH_NBR = '00003022'
AND ( MBR304_TM_PH_SUMM.PH_DIMSN_KEY=PLH300_PH_DIMSN.PH_DIMSN_KEY  )
AND 
(EXISTS
  (
    SELECT *  
    FROM PLH300_PH_DIMSN p2

    WHERE
      ( 
             ( p2.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
        AND  ( p2.PH_DIMSN_KEY = MBR304_TM_PH_SUMM.PH_DIMSN_KEY )
        AND  ( p2.PH_CAT_RMC_CD  =  '9 ')
        AND  ( p2.PH_CAT_MED_TYP_CD LIKE 'S%' )
        AND  ( p2.PH_CAT_MED_TYP_CD NOT IN ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA') )
      )
  )
)

AND 

(EXISTS
  (
    SELECT *  
    FROM PLH300_PH_DIMSN p1

    WHERE
      ( 
                  ( p1.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
        AND  ( p1.PH_DIMSN_KEY = MBR304_TM_PH_SUMM.PH_DIMSN_KEY )
        AND  ( p1.PH_CAT_RMC_CD  =  '9 ')
        AND  ( p1.PH_CAT_MED_TYP_CD NOT LIKE 'S%' 
                 OR
               p1.PH_CAT_MED_TYP_CD IN ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA') 
                 )
      )
  )
)

GROUP BY
  PLH300_PH_DIMSN.PH_NBR, 
  PLH300_PH_DIMSN.PH_CAT_RMC_CD

 

[SantaMufasa]

Rusty,

Whenever we receive unexpected results from a query, and if we cannot explain those results, then the most effective method for isolating the problem has been, for me, "breaking" the query up into reasonable components to check the incremental results.

In your case, this is what I would do:

1) Identify a value of PLH300_PH_DIMSN.PH_NBR that triggers results that you expect. (And for this example, let's presume a value of "74".

2) Then run the following queries:

SELECT count(*)  
  FROM PLH300_PH_DIMSN p2
 WHERE ( p2.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
   AND ( p2.PH_DIMSN_KEY = MBR304_TM_PH_SUMM.PH_DIMSN_KEY )
   AND ( p2.PH_CAT_RMC_CD  =  '9 ')
   AND ( p2.PH_CAT_MED_TYP_CD LIKE 'S%' )
   AND ( p2.PH_CAT_MED_TYP_CD NOT IN
         ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA'));

Also run the second subquery:

SELECT count(*)  
  FROM PLH300_PH_DIMSN p1
 WHERE ( p1.PH_NBR  =  PLH300_PH_DIMSN.PH_NBR )
   AND ( p1.PH_DIMSN_KEY = MBR304_TM_PH_SUMM.PH_DIMSN_KEY )
   AND ( p1.PH_CAT_RMC_CD  =  '9 ')
   AND ( p1.PH_CAT_MED_TYP_CD NOT LIKE 'S%' 
         OR
         p1.PH_CAT_MED_TYP_CD IN
         ('SE ','SH ','S3A','S7A','SRA','STA','SXA','SYA'));

If the results for either code snippet produce "0" for their counts, then you should focus on the respective query, further removing/adjusting WHERE-clause components until you receive the results you should be receiving.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

HI Mufasa,

Yep, I had tried that already and that was the frustrating part, they both brought in "1".

I just found the problem though. I was under the impression that "PH_DIMSN_KEY" was a unique key for each "PH_NBR".

However, it turns out, "PH_DIMSN_KEY" is unique for each row. Therefore, that key cannot exists in both scenarios at the same time.

I should of checked the raw data in the table first (the basics), but being in a hurry....ah well.

Thanks for your help though, very appreciated!!

 

[jimirvine]

a TRUE response to the question, "EXISTS", results from its associated query returning any NOT NULL value from the associated SELECT sub-query. In this case, I chose 'rusty' and 'afro' to be the arbitrary NOT NULL value ...[snip]

What about:

select distinct uniqueid from tblsample a
 where exists (select NULL from tblsample
                where a.uniqueid = uniqueid
                  and product = 'a')
   and exists (select NULL from tblsample
                where a.uniqueid = uniqueid
                  and product = 'b');

(Sorry to be picky Dave ) but you don't need to use a non-null value as the return.

Jim

 

[SantaMufasa]

Jim,

Excellent catch...After using Oracle for nearly 20 years, that's the first time I have become aware that a NULL will also work on an EXISTS operator...EXISTS simply needs at least a single resulting row, regardless of its value or lack thereof, to produce a TRUE result.

Thanks, Jim...Hava

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[RustyAfro]

Ah, that is good to know, it has better syntax. Thanks for the FYI, star to ya too.

 

[jimirvine]

Glad to help. I think that it is you, Dave that deserves the star (as usual) though.

 

[SantaMufasa]

Very gracious of you, Jim...Thanks!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]