znkin (9/16/2012)

---

I am using SQL 2008 and I need help to write a select query for the following output

I need list of distinct CustId who have ordered both ProdId 1 and 4. Also if the custid has ordered ProdId 0 then that custid should not be selected

table schema and data are given below and as per the data given the select query should display data cust1 and cust5 as both of them have ProdId 1 and 4 and also neither of them have ProdId 0.

create table product_details

(

TempId int identity,

CustId varchar(50),

ProdId char(1),

enteredon datetime

)

insert into product_details values('cust1',1,getdate())

insert into product_details values('cust1',2,getdate())

insert into product_details values('cust1',3,getdate())

insert into product_details values('cust1',4,getdate())

insert into product_details values('cust2',1,getdate())

insert into product_details values('cust2',2,getdate())

insert into product_details values('cust2',3,getdate())

insert into product_details values('cust3',0,getdate())

insert into product_details values('cust3',1,getdate())

insert into product_details values('cust3',2,getdate())

insert into product_details values('cust4',1,getdate())

insert into product_details values('cust4',2,getdate())

insert into product_details values('cust4',3,getdate())

insert into product_details values('cust4',4,getdate())

insert into product_details values('cust4',0,getdate())

insert into product_details values('cust5',1,getdate())

insert into product_details values('cust5',2,getdate())

insert into product_details values('cust5',3,getdate())

insert into product_details values('cust5',4,getdate())

thanks