Select if more than one record found

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

Select if more than one record found

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2009-12-10 : 11:22:57

Is there a way to select records like this:

Sales Order Table
OrderID StoreID Amount
100 5050 101.00
101 6010 120.00
102 5050 99.00
103 7010 88.00

Select the record if Amount > 100
OR
Select all records for a store if the store has an Amount > 100

So, in this example the query should return:
Order ID
100
101
103

I'm trying to get this done in one SQL query or in a View. Using MS-SQl 2005.

Thanks!
MD

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 11:29:28

Why would 103 be returned in the example?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-10 : 11:30:35

There is way. Has your instructor covered the HAVING clause yet?

Jim

Everyday I learn something that somebody else already knew

MevaD
Starting Member

32 Posts

Posted - 2009-12-10 : 12:02:43

Ryan: Sorry, 102 would be returned, not 103.

MevaD
Starting Member

32 Posts

Posted - 2009-12-10 : 12:03:36

Jim: No, we haven't covered HAVING clause.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 12:10:49

Any of these will do it (and there are more ways too)...

declare @SalesOrder table (OrderID int, StoreID int, Amount money)
insert @SalesOrder
          select 100, 5050, 101.00
union all select 101, 6010, 120.00
union all select 102, 5050, 99.00
union all select 103, 7010, 88.00

select * from @SalesOrder where StoreId in (select distinct StoreId from @SalesOrder where Amount > 100)
select * from @SalesOrder where StoreId in (select StoreId from @SalesOrder where Amount > 100)
select * from @SalesOrder a where exists (select * from @SalesOrder where Amount > 100 and StoreId = a.StoreId)

...unless you need to consider total amounts, in which case you will probably need to use group by and the having clause.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

MevaD
Starting Member

32 Posts

Posted - 2009-12-10 : 12:26:25

I don't need totals. I'll try your examples.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-10 : 12:43:28

declare @SalesOrder table (OrderID int, StoreID int, Amount money)
insert @SalesOrder
select 100, 5050, 101.00
union all select 101, 6010, 120.00
union all select 102, 5050, 99.00
union all select 103, 7010, 88.00

SELECT s.*
FROM
@salesorder s
INNER JOIN

(
select storeid,[Amount] = sum(amount) from @salesorder
group by storeid
having sum(amount) >= 100
) t1

ON
s.storeid = t1.store

Jim

Everyday I learn something that somebody else already knew

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 12:51:34

You missed an 'id' off, Jim: t1.storeid

Here's another option if that's the requirement:

select * from @SalesOrder a where exists (select * from @SalesOrder where StoreId = a.StoreId having sum(Amount) > 100)

(Not sure if it should be > or >= either)

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

MevaD
Starting Member

32 Posts

Posted - 2009-12-10 : 13:00:43

Thanks guys - that works great!! I learned something new today!

MD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources