Using something for all - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using something for all

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2005-07-16 : 21:41:35

Use Northwind
go
select c.customerid,c.companyname,o.orderid,od.productid,p.productname
from customers c
join orders o on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on p.productid=od.productid and p.productid in (17,40)
order by c.customerid

Hello ,using this query i am getting the both 17,40.
It acts like a 'or' operator.I want to return the resultset which has got both 17,40.Effectively I want 'and'operator.
Can someone write the query for this.
Thank you.

Kristen
Test

22859 Posts

Posted - 2005-07-17 : 05:26:40

Not sure this is the most efficient method, but hopefully it will work!


SELECT O.orderid
FROM orders O
WHERE 2 =
      (
          SELECT COUNT(distinct OD.productid)
          FROM   [order details] OD 
          WHERE      OD.orderid = O.orderid
                 AND OD.productid IN (17, 40)
      )

Kristen

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2005-07-17 : 17:20:35

Thank you kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources