I'm trying to make a query that returns some OS's from the database, but it's necessary to satisfying one condition. The data in my database is like:
| ID | STATUS_ID | CLIENT |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 4 | 1 | 3 |
What I want is that my query returns OS's by client where status = 2 or status = 1 if don't have any OS from the same client with status = 2.
The result that I want is:
| ID | STATUS_ID | CLIENT |
|---|---|---|
| 1 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 1 | 3 |
The query I'm trying to do is like this (I know the query is wrong):
select *
from os
where status_id = 2
or (
status_id = 1
and not exists (
select id
from os
where client_id = os.client_id
and status_id = 2
)
)
Answer
You can use an UNION operation for that.
select * from os where status_id = 2
union
select * from os x
where x.status_id = 1
and not exists (select 1 from os a where status_id=2 and a.client = x.client);
See it working here: https://sqlfiddle.com/mysql/online-compiler?id=9d888bec-b4ba-4827-84f5-03eee17b1bee