query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-03-13 : 11:10:46

orderid productid quantity
100 1 2
100 2 1
100 3 1
101 1 4
102 3 2

output should be: orderid
100

I want the orderids which has got
productids 1 and 2 both for that particular
order. Like for example,from the above
table only orderid 100 has productids
1 and 2. I want the output as 100.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-13 : 12:13:27

select distinct orderid
from table t1
where productid=1
and exists(select * from table t2 where t2.orderid=t1.orderid and t2.productid=2)

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:11:29

[code]
select orderid
from table
where productid in (1,2)
group by orderid
having min(productid) <> max(productid)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-03-13 : 15:28:00

SELECT OrderID
FROM Table
GROUP BY orderid
having SUM( case when ProductId = 1 THEN 1 ELSE 0 END) > 0
and SUM( case when ProductId = 2 THEN 1 ELSE 0 END) > 0

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:53:29

this variant also works


select orderid
from table 
where productid in (1,2)
group by orderid
having count( distinct productid) =2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources