Q

Another WHERE EXISTS example

I selected what I need from PARTDATA table and put some conditions and this is what my data looks like so far...

PDate   Part   Data    Results  Final
11-Feb  11234  Length   22.6    OK
11-Feb  11234  Width     2.3    NOT OK
11-Feb  11234  Height   34.67   OK
14-Feb  11222  Length   25.60   OK
14-Feb  11222  Width     4.20   OK
14-Feb  11222  Height   35.00   OK
15-Feb  11546  Length   23.00   OK
15-Feb  11546  Width     4.30   OK
15-Feb  11546  Height   45.00   NOT OK
16-Feb  12789  Length   24.50   OK
16-Feb  12789  Width     5.00   OK
16-Feb  12789  Height   33.70   OK

But I'm trying to get a list of Parts that have a "NOT OK" under Final column.

PDate   Part   Data    Results  Final
11-Feb  11234  Length   22.6    OK
11-Feb  11234  Width     2.3    NOT OK
11-Feb  11234  Height   34.67   OK
15-Feb  11546  Length   23.00   OK
15-Feb  11546  Width     4.30   OK
15-Feb  11546  Height   45.00   NOT OK

Is it possible? Thanks!


Yes, it's easy. Basically, your query looks something like this:

select PDate
     , Part
     , Data
     , Results
     , Final
  from PARTDATA
 where some conditions 
order 
    by PDate
     , Part

Now all you have to do is give the table a table alias, which you then use as a correlation variable in a correlated subquery:

select PDate
     , Part
     , Data
     , Results
     , Final
  from PARTDATA as O
 where some conditions 
   and exists
       ( select *
           from PARTDATA
          where PDate = O.PDate
            and Part  = O.Part
            and some conditions 
            and Final = 'NOT OK' ) 
order 
    by PDate
     , Part

The subquery examines all the rows in the same group as each row in the outer query. The groups are defined by the same values of PDate and Part. Each row in the subquery is correlated to each outer row in its group. All that's required to get a TRUE result for the EXISTS condition is for any one of the correlated rows in the group to match the 'NOT OK' condition. If none of the correlated rows matches, then all the rows from that particular PDate and Part group will be excluded from the results.

A correlated subquery thus performs grouping. It just doesn't use a GROUP BY clause to do so.


This was first published in February 2005

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close