noob February 2016

MySQL : Getting Entries from a many-to-many table

I have a following table, I would like to select records which should have all the mentioned values. For example select CompanyID which should have all the three ProviderID 200,300,400. So running the query should retrun CompanyID 2. Something the opposite of IN. Thanks in advance !

CompanyID      |        ProviderID
--------------------------------------
    1                 100
    2                 200
    3                 500
    4                 600
    2                 300
    2                 400
    7                 100

Answers


jarlh February 2016

Do a GROUP BY, select only rows with any of the 3 wanted EmployeeID values. Use HAVING to make sure to get 3 different EmployeeID values.

select CompanyID
from tablename
where EmployeeID IN (200, 300, 400)
group by CompanyID
having count(distinct EmployeeID) = 3

Another approach is a double self join, one for each EmployeeID. However, this is less flexible:

select distinct t1.CompanyID
from (select CompanyID from tablename where EmployeeID = 200) t1
join (select CompanyID from tablename where EmployeeID = 300) t2
    on t1.CompanyID = t2.CompanyID 
join (select CompanyID from tablename where EmployeeID = 400) t3
    on t2.CompanyID = t3.CompanyID

Post Status

Asked in February 2016
Viewed 3,033 times
Voted 12
Answered 1 times

Search




Leave an answer