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 !
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.
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
Asked in February 2016Viewed 3,033 timesVoted 12Answered 1 times