Simple Select 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

Simple Select query ?

Author

Topic

Nroblex
Starting Member

17 Posts

Posted - 2009-10-23 : 05:10:42

Hello Gurus,

I have a question, perhaps very simple but I'm stuck..

I have a table like this:
[OrderNumber], [statusValue], [...], [...], [...], [...]

Foreach Ordernumber there can be many statusvalues. My question is how to select just statusvalues of a certain type; for instance all records of an order that have just 1 and 70 ...

I Have this :

This recordset is compleate.
[OrderNo], [statusValue], [...], [...], [...], [...]
37737 1
37737 20
37737 40
37737 50
37737 70

this recordset lacks status 20, 40, 50.
[OrderNo], [statusValue], [...], [...], [...], [...]
37738 1
37738 70

So I want to write a smart SQL-query to select all records that have just status 1 and 70.

Many Thanks in advance
Nroblex

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 05:17:53

select OrderNo,statusValue
from table t1
where statusValue in (1,70)
and exists(select * from table t2 where t2.OrderNo = t1.OrderNo and statusValue=1)
and exists(select * from table t2 where t2.OrderNo = t1.OrderNo and statusValue=70)

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

Nroblex
Starting Member

17 Posts

Posted - 2009-10-23 : 05:31:03

Thanks webfred, but it does not seem to work ?? When I run your query I get ordernumbers that contains other statusvalues than 1 and 70. //nroblex

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 05:51:03

So you want your output to show only entries where nothing else than 1,70 exists?

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

Nroblex
Starting Member

17 Posts

Posted - 2009-10-23 : 06:15:48

Yes! That's what I want.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 06:42:59

select
OrderNo,
statusValue
from table t1
join
(select OrderNo from table where statusValue in (1,70) group by OrderNo having count(*)=2)dt
on dt.OrderNo=t1.OrderNo

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

Nroblex
Starting Member

17 Posts

Posted - 2009-10-23 : 08:37:13

webfred, thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 08:39:38

welcome