Selecting multiple rows from the same table with common column?

Have the following hypothetical table User_Mail_Info in Oracle DB

| 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.

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;