I have two tables:
Project:
| Id | Name |
|---|---|
| 1 | ABC |
| 2 | DEF |
| 3 | GHI |
| 4 | JKL |
| 5 | MNO |
| 6 | PQR |
Attachment:
| Id | Status | ProjectId |
|---|---|---|
| 1 | a1 | 1 |
| 2 | a1 | 1 |
| 3 | a2 | 2 |
| 4 | a2 | 2 |
| 5 | a1 | 3 |
| 6 | a2 | 3 |
| 7 | a1 | 4 |
| 8 | a2 | 4 |
| 9 | a3 | 4 |
| 10 | a1 | 5 |
| 11 | a2 | 5 |
| 12 | a4 | 5 |
I'd like to get projectnames which has assignments only with statuses a1 and a2 (must have both of them) and doesn't havve assignments in statuses a3 and a4. So the result should be:
GHI
CodePudding user response：
You can use exist|not exists keywords to achieve this.
select [Name] from Project t1
where
exists (select 1 from Attachment where [Status] = 'a1' and ProjectId = t1.Id)
and
exists (select 1 from Attachment where [Status] = 'a2' and ProjectId = t1.Id)
and
not exists (select 1 from Attachment where [Status] = 'a3' and ProjectId = t1.Id)
and
not exists (select 1 from Attachment where [Status] = 'a4' and ProjectId = t1.Id)
CodePudding user response：
select Name from
(select
P.Name,
string_agg(Status,',')
WITHIN GROUP (ORDER BY Status) Status
from Project P
join Attachment A
ON A.ProjectId=P.Id
group by P.Name) t
where Status='a1,a2'