Possible sub 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

SQL Server 2005 Forums

Transact-SQL (2005)

Possible sub select query?

Author

Topic

jmac81
Starting Member

2 Posts

Posted - 2008-12-22 : 14:01:40

I'm really bad at explaining things, but here it goes...

I've got a table that looks similar to this (to make it simple)

order_num line_num start_dt end_dt
123 1 1/1/08 1/2/08
123 2 1/1/08 <null>
123 3 1/1/08 <null>
456 1 1/1/08 <null>

I'm trying to pull any record where the end_dt is null and the count(order_num) > 1. So, out of this data set, I would like to pull the follwing records:

123 2 1/1/08 <null>
123 3 1/1/08 <null>

I tried doing a sub select and not including the line number in the sub select statement, but it's still not recognizing the count as anything greater than 1 because it seems to be counting each record regardless if the order_num is the same.

select order_num, line_num, start_dt, end_dt
from order
where exists
(select order_num
from order
where end_dt is null
group by order_num
having count(order_num) > 1)

But it does not give me the correct data.

Is there any way to do this?

I really appreciate your help!!!

Thanks,

Jessa

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-22 : 14:16:14

At a short first look i would say that there is no relationship between outer and sub query.
Test it like this:

select order_num, line_num, start_dt, end_dt 
from order o1
where exists
(select order_num
from order o2
where o2.end_dt is null
and o2.order_num = o1.order_num
group by order_num
having count(order_num) > 1)

Webfred

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

PingTheServer
Starting Member

28 Posts

Posted - 2008-12-22 : 14:28:04

[code]
select order_num, line_num, start_dt, end_dt
from order
where end_dt is null
group by order_num
having count(order_num) > 1
[/code]

Does that work?

jmac81
Starting Member

2 Posts

Posted - 2008-12-22 : 15:48:02

I got it!

select
o1.order_num,
o1.line_num,
o1.start_dt,
o1.end_dt
from
orders o1,
(select order_num, count(*) from orders where end_dt is null group by order_num having count(*) > 1) o2
where
o1.order_num = o2.order_num
and o1.end_dt is null

Thanks for your help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources