I have a single table (Tracker) containing 2 columns; (ID and Status). I would like to retrieve a list of ID's without the status approved, however a status can go from pending to approve and the original data will not be removed. See below example and desired outcome.
Tracker Table:
| ID | Status |
|---|---|
| 123 | Pending |
| 123 | Approved |
| 456 | Pending |
| 678 | Denied |
| 894 | Approved |
Desired Outcome:
| ID | Status |
|---|---|
| 456 | Pending |
| 678 | Denied |
123 is not present due to the second status being 'Approved' and 894 is not present because the only status in 'Approved'
Was thinking along the line of something like below:
Select ID
FROM Tracker b
WHERE Status = 'Approved'
Right Join
Select ID
FROM Tracker a
USING ID
SELECT T.ID,T.STATUS
FROM YOUR_TABLE AS T
WHERE NOT EXISTS
(
SELECT 1 FROM YOUR_TABLE AS T2 WHERE T.ID=T2.ID AND T2.STATUS='APPROVED'
)