EXPERT RESPONSE
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.
|
