Question 354545 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Trying to get record when a correlated record doesn't exist

I’m trying to get a record from a table, but only want to retrieve that record if there isn’t another corresponding record with another status.

Schema:

ID: PK

RID: Collection ID

IID: Item ID

Each ItemID correlates to a record

An example of what the records could be:

ID RID IID Status
1 14 1000 Available
1 14 1000 Deleted
1 14 1001 Available
1 14 1001 Deleted
2 15 2000 Available
2 15 2000 Deleted
2 15 2001 Available

As you can see in the table for RID: 15, We have an IID which is Available and DOES NOT have Deleted.

I want to be able to select that row. Essentially I want to return only the record where and instance has an Available, but no corresponding Deleted record for that same instance.

Right now, I’ve been querying all of the data and then doing some business logic in code to determine it, but I would rather filter this at the database level.

I know I’m meant to be using a subquery but haven’t got it working yet.

Any help appreciated, SQL isn’t my strong point.

Tags:

2

Answers


  1. 0 
    SELECT *
FROM test t1
WHERE status = 'Available'
  AND NOT EXISTS (
    SELECT NULL
    FROM test t2
    WHERE t1.ID = t2.ID
      AND t1.RID = t2.RID
      AND t1.IID = t2.IID
      AND t2.status = 'Deleted'
    )
    ID RID IID Status
    2 15 2001 Available 
    SELECT ID, RID, IID, 'Available' status
FROM test
GROUP BY ID, RID, IID
HAVING MAX(status = 'Available')
   AND NOT MAX(status = 'Deleted')
    ID RID IID status
    2 15 2001 Available

    fiddle

    Login or Signup to reply.
  2. 0

    See example

    select *
from(
select *
  ,lead(Status,1,'Absent')over(partition by ID,RID,IID 
      order by Status)nextStatus
from test
)x
where Status='Available' and nextStatus='Absent'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search