MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (ID INT, RID INT, IID INT, Status VARCHAR(10));
INSERT INTO test VALUES
(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');
SELECT * FROM test;

Records: 7  Duplicates: 0  Warnings: 0




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


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

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