Select a row from different conditions

1

Hello!

I have two tables joined. Table1 and table2 the resulting table is describe below. I want to select all rows with shifttypeId = 1 and all rows with shifttypeID = 2 but only if the previousshiftID doesnt exist in a transaction where Shifttype ID = 1. For example. In the case below i only want to select transaction 1, 2, 3 but not transaction 4 because the previousShiftId exists in transaction 4.

Transaction, Operator, ShiftId, PreviousShiftId and ShiftypeID
1 23 1 - 1
2 23 3 2 2
3 23 4 - 1
4 23 5 4 2

Can u Help me? =) I Have no idea what to do, i have tried everything that i could think of..

3

Are transaction numbers strictly increasing? That is, will a previous transaction always have a lower number? If not, is there a date field to identify previous transactions?

4 
SELECT *
FROM result r
WHERE (ShiftypeId = 1
   OR (ShiftypeId = 2 AND NOT EXISTS(
         SELECT 1 FROM result r2 
          WHERE r2.ShiftId = r.PreviousShiftId AND r2.ShiftypeID = 1)))
5

Hello!

Thank for your help!. The Transaction numbers are not strictly increasing because there are other transactions in different tables who also contributes to Transaction

ScottPletcher. Im sorry, i did not get that to work. The thing is that ShiftId, PreviousShiftId can be found many times in the table, but with different Operator. I need to select PreviousShiftId, ShiftId where operator is the same!

6

I guess include Operator in the NOT EXISTS checks:

SELECT *
FROM result r
WHERE (ShiftypeId = 1
   OR (ShiftypeId = 2 AND NOT EXISTS(
         SELECT 1 FROM result r2 
          WHERE r2.ShiftId = r.PreviousShiftId AND r2.Operator = r.Operator AND
              r2.ShiftypeID = 1)))