sql - Select all rows in a group where within the group, one column has one specific value, and another column has another specific value on the next row

Question

I am new to SQL, so I’m not sure which approach is best for this kind of task:

I have a table where groups of rows all relate to the same item, whose name appears in the first column. Other details appear in the other columns. I am trying to retrieve all rows for every group based on having the same value in the first column, where every time a certain value appears in one column, another value appears in a different column in the following row.

 | Fruit  | Value1| Value2|
 --------------------------
 | APPLE  |  A    |       |
 | APPLE  |       | E     |
 | PEAR   |  A    |       |
 | PEAR   |       | X     |
 | FIG    |       | X     |
 | FIG    |  A    |       |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | GRAPE  |       | X     |
 | GRAPE  |       | T     |
 | ORANGE |  A    |       |
 | ORANGE |       | X     |
 | ORANGE |       | Y     |
 | ORANGE |       | Z     |
 | PEACH  |  B    |       |
 | PEACH  |  A    |       |
 | PEACH  |       | X     |
 | MANGO  |  B    |       |
 | MANGO  |  C    |       |
 | MANGO  |  D    |       |

From the above table, I would like to select all rows for a given Fruit, where Value1 is A on one row, Value2 is X on the following row, and nothing other than A appears in Value1 on any row for that Fruit.

From the above table, the query should deliver results that look like this:

 | Fruit  | Value1| Value2|
 --------------------------
 | PEAR   |  A    |       |
 | PEAR   |       | X     |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | CHERRY |  A    |       |
 | CHERRY |       | X     |
 | ORANGE |  A    |       |
 | ORANGE |       | X     |
 | ORANGE |       | Y     |
 | ORANGE |       | Z     |

• APPLE is excluded because on the row after the one where Value1=A, Value2!=X.
• FIG is excluded because Value2=X occurs on the row before Value1=A, instead of the row after.
• GRAPE is excluded because there is no row where Value1=A.
• PEACH is excluded because there is at least one row where Value1!=A.
• MANGO is excluded because there is no row were Value1=A, and because there is no row where Value2=X.

The part that seems a bit tricky to me is performing several checks at the level of the group of rows, but still returning all the rows of the matching group.

Thanks in advance for tips and suggestions. Let me know if you need me to clarify the question. The database is DB2 V10 on z/OS.

Answer 1

Except for the FIG (in your example), which requires an order column (id or whatever), the query bellow solves your problem:

select * from fruits 
where fruit not in (select fruit from fruits where value1 is not null and value1 <> '' and value1 <> 'A')
and exists (select fruit from fruits f2 where f2.fruit = fruits.fruit and value2 = 'X')