A self-contained subquery is a query that can run independently of the outer query. Scalar (single-value) subqueries can appear anywhere in the query where an expression resulting in a scalar value is expected. A scalar subquery is valid when it returns a single value or no values. However, if a scalar subquery returns more than one value, a run-time eror will occur.
For example, the subquery returns a single value(1) and the outer query returns all orders with employeeID 1. If this subquery were to return more than one record, say (where lastname like 'd%', then it would return an error.
select orderid, employeeid
from orders
where employeeid = (SELECT employeeid from employees
where lastname like N'davolio')
One way to solve this is to use a technique using GROUP BY and DISTINCT COUNT. This query finds all orders with one of the five employeeIDs, groups those orders by CustomerId and returns
CustomerIDs tht have all five distinct employeeID values in their orders.
select customerID from
orders
where employeeid in (1,2,3,4,8)
group by customerid
having count(distinct employeeid) = 5
But if you don't know the list of employeeID values in advance or there is a large list of them, then this approach is kind of useless. to make this statement for dynamic and useful, you can use subqueries instead of literals.
select customerID from
orders
where employeeid in
(Select employeeID from employees where country = 'usa')
group by customerid
having count(distinct employeeid) =
(Select Count(*) from employees where Country = 'usa')