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!


    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top