Select Statement

Transact-SQL
1

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

2

something like this perhaps:

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
INNER JOIN gprdsql.ColProcessing ON gprdsql.ColProcessing.col_uid = right(gprdsql.TblCollections.col_uid, 2)
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
3

The above will pull the record - please note that was just an example - I have thousands of records and expect more than one record.

Thank you

4

fine, the extra join will give you as many rows as there are matches