I have two tables t1 and t2
In t1, I have two variables, ID (which uniquely identify each row) and DOC (which can be common to several IDs)
In t2, I have three variables, ID (which does not necessarily uniquely identify the rows here), AUTH , and TYPE. Each ID has a maximum of 1 distinct AUTH.
What I would like to do is to select the DOCs that have an ID with AUTH='EP', and that also have an ID with AUTH='US'. They could have additional IDs with other AUTH, but they have to have at least these two.
Thus, i would have a final table with the DOC, ID,and AUTH (there should be at least 2 IDs per doc, but it can be more if there exists an additional AUTH to US and EP for this DOC)
The desired results:
This should work:
SELECT DISTINCT (T1.ID), T1.DOC, T2.AUTH FROM T1 LEFT JOIN T2 ON T2.ID = T1.ID WHERE T1.DOC IN( SELECT T1.DOC FROM T2 LEFT JOIN T1 ON T1.ID = T2.ID WHERE T2.AUTH IN('EP','US') GROUP BY T1.DOC HAVING COUNT(DISTINCT T2.AUTH) = 2) ;