Hi,
I have a view;
SELECT TOP (100) PERCENT gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.prac_eid, gprdsql.TblPracDetails.prac_status, gprdsql.TblCollections.col_type,
gprdsql.TblCollections.col_date, gprdsql.TblCollections.col_uid, gprdsql.TblCollections.Stage
FROM gprdsql.TblPracDetails INNER JOIN
gprdsql.TblCollections ON gprdsql.TblPracDetails.prac_no = gprdsql.TblCollections.prac_no
WHERE (gprdsql.TblCollections.col_type = 'FDC3') AND (gprdsql.TblCollections.Stage = 'Checked')OR (gprdsql.TblCollections.Stage = 'Loaded')
GROUP BY gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.prac_eid, gprdsql.TblPracDetails.prac_status, gprdsql.TblCollections.col_type,
gprdsql.TblCollections.col_date, gprdsql.TblCollections.col_uid, gprdsql.TblCollections.Stage
ORDER BY gprdsql.TblPracDetails.prac_no
Results
prac_no prac_eid prac_status col_type col_date Col_uid Stage
1 11 Active FDC3 30-03-15 15 Checked
2 12 Active FDC3 30-03-15 16 Checked
3 13 Active FDC3 30-03-15 17 Loaded
Now, On gprdsql.TblCollections I have;
prac_no col_type col_date Col_uid Stage
1 FDC3 30-03-15 15 Checked
1 IDC 01-04-15 54 Checked
2 FDC3 30-03-15 16 Checked
2 IDR 02-05-15 55 Checked
And I have another table (not included yet in the above View) gprdsql.ColProcessing, I have
col_uid Stage
15 Stage 8
54 Stage 8
16 Stage 6
55 Stage 8
Now I want to extract;
- First to extract all col_uids records with FDC3 and Stage = Checked, then find the subsequent IDC that have a Stage 8 ONLY.
AT our example above; the result will be (only one record - but in production this could be thousands please consider tables with 1000's of records)
col_uid Stage
54 Stage 8
How can I update the above query to extract ONLY this record - thanks in advance