Have the following hypothetical table User_Mail_Info in Oracle DB
x
| User_Id | Mail_DL | DLId | ..
--------------------------------
| User1 | DLA | 1 |
| User1 | DLB | 2 |
| User2 | DLA | 1 |
| User2 | DLB | 2 |
| User2 | DLC | 3 |
| User3 | DLA | 1 |
| User3 | DLC | 2 |
| User3 | DLB | 3 |
Primary key is User_Id,Mail_DL,DLId. There are other columns with the table as well.
How to retrieve the UserId ,given the list of (Mail_DL and DL Id) ?
Note: Mail_DL and DL_Id are not directly related. This is just hypothetical data
For eg. Input is
- (DLA, 1), (DLB,2) – Output has to be User1
- (DLA, 1), (DLB,2), (DLC,3) – Output has to be User2
- (DLA, 1), (DLC,2), (DLB, 3) – Output has to be User3
Thanks in advance for helping out.
Advertisement
Answer
You can use aggregation and filtering. For the first example:
select user_id
from t
group by user_id
having sum(case when mail_id = 'DLA' and dlid = 1 then 1 else 0 end) > 0 and
sum(case when mail_id = 'DLB' and dlid = 2 then 1 else 0 end) > 0 and
count(*) = 2;