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