Complex select query help

[eerich]

Hello,

I have the following scenario that I'm trying to solve but have run into trouble retrieving the correct results.

I have a table that has data including a IDnum, amount, description. Within each IDnum, there may be from 1 to 100 different amounts and descriptions. Example

IDNUM Amount Desc
0001 81000 Test entry 1
0001 4000 Test sub 6
0001 19000 Test sub3
0001 -2850 Test games
0002 29555 Book club
0003 88000 Europe planning
0003 35000 DP on Furnishing

Now, what my query needs to do is select all line items with in the IDNum where at least one IDnum contains an amount > 80000.

So using the above example, I would need to return all 4 lines items of IDnum 0001 and both line items of 0003.

Select IDNUM, Amount, Description
From myTable
Where amount In
(select *
from myTable
Having amount >80000
group by IDNum, description));

My query is only returning the value with the single line above the threshhold of 80000. I haved tried using the group by; having; and exists clause, but I can't seem to get the correct results.

Any help would be greatly appreciated.

Thanks

 

[r937]

select IDNUM, Amount, Description
  from myTable as t1
 where exists
       ( select *
           from myTable
          where IDNum = t1.IDNum
            and amount > 80000 )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[eerich]

That worked perfectly!

Thank you very much.