llauc llauc - 2 months ago 8
MySQL Question

Mysql: select value that matches several criteria on multiple rows

Good evening,

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.

Sample data:



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:


Answer

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) ;