biviz biviz - 6 months ago 38
SQL Question

SQL: return group of rows where condition is true

My dataset looks like this:

PNR Action Code
-----+-------------
1 | Book
1 | Exchange
1 | Cancel
2 | Book
2 | Exchange
3 | Book
4 | Book
4 | Cancel


I am trying to return all the rows for PNRs where Action code was "Cancel". So in this case, all the rows for PNRs 1 & 4.

I tried to use a Where Clause but that won't be correct as it would return only the rows WHERE action code was "Cancel". My result should look like:

PNR Action Code
-----+-------------
1 | Book
1 | Exchange
1 | Cancel
4 | Book
4 | Cancel

Answer Source

Perhaps get the PNRs that you want from a select statement in your Where clause?

Select T1.PNR
      ,T1.[Action Code]
From myTable T1
Where T1.PNR in (
       Select PNR
       from myTable
       where [Action Code] = 'Cancel'
       )
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download