>Hi Hugo,
>
>and now - generic for n items ....
>
>w/o macro or the like
Good question. I see the errors of my ways... In that case I would use Rich's version, I think, cannot give it much thought now though.
create table #Customers (CustomerID int, CustomerName varchar(50))
create table #Products (ProductID int, ProductName varchar(50), Price Money)
create table #Sales (ProductID int, CustomerID int, Quantity int)
create table #List (ProductID int)
insert into #Customers (CustomerID, CustomerName) values (1, 'Robert'), (2, 'James'), (3, 'Anderson')
insert into #Products (ProductID, ProductName, Price) values (1, 'Apple', 1), (2, 'Orange', 5.3), (3, 'Grapes', 2)
insert into #Sales (ProductID, CustomerID, Quantity) values (1, 1, 1), (1, 2, 3), (2, 1, 2), (3, 1, 5), (2, 2, 2), (1, 1, 1)
insert into #List (ProductID) values (1), (3)
select Cust.CustomerId,
Cust.CustomerName
from #Customers Cust
inner join #Sales S On Cust.CustomerId = S.CustomerId
where S.ProductID IN (select ProductID from #List)
group by Cust.CustomerID, Cust.CustomerName
having count(distinct(ProductID)) = (select count(*) from #List)
drop table #Customers
drop table #Products
drop table #Sales
drop table #List
"The five senses obstruct or deform the apprehension of reality."
Jorge L. Borges?
"Premature optimization is the root of all evil in programming."
Donald Knuth, repeating C. A. R. Hoare
"To die for a religion is easier than to live it absolutely"
Jorge L. Borges
