help modifying sp - 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 2000 Forums

Transact-SQL (2000)

help modifying sp

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-09-17 : 20:01:03

I have the stored procedure below I just need to add one thing. It seems simple but I keep getting it wrong.

What I need to add is 2 columns (minCost, and maxCost)

The minCost and maxCost columns are in the TblProducts table. I can join onto this table from "zoneID"

"zoneID" is in both tblProducts and tblZones

Any help is greatly appreciated.

Thanks alot guys

mike123

CREATE PROCEDURE [select_TopPerformers]
(
@siteDirID tinyint
)

AS SET NOCOUNT ON

SELECT * from tblZones Z JOIN tblCategories C on Z.categoryID = C.categoryID JOIN tblParentCategories P on c.parentCategoryID = p.parentCategoryID WHERE siteDirID = @siteDirID

GO

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-09-17 : 22:49:07

Mike123, where's the catch?

You say yourself that you can join it, so what's the problem? Please post current results, and expected results.

Sarah Berger MCSD

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-09-17 : 22:55:15

I could try to guess at your data structure but instead I'll ask you to post some ddl and dml to recreate your example and I'll also ask if max and min are actual columns in your table or you want to calculate max and min. If the latter then you need to use the MIN() and MAX function ie:

SELECT MIN(cost), MAX(cost)
FROM tblX

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-09-17 : 22:58:00

While testing I found that if I create a table with one column called cost no data and I run the following

SELECT MAX(cost) AS Expr1, MIN(cost) AS Expr2
FROM Table1

the result set is Empty, NULL

Is this correct?Shouldn't I get NULL, NULL

What's up?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-09-17 : 23:45:24

Hmm yes that is weird valter

I keep playing with the SP, but I can't seem to get it. This is where I am at right now. I tried changing the way I used the min and max as you suggested.

I've included sample data below

Thanks for the help!
mike123

alter PROCEDURE [select_TopPerformers]
(
@siteDirID tinyint
)

AS SET NOCOUNT ON

SELECT Z.zoneID, Z.userID, Z.websiteName, Z.URL, Z.description, Z.categoryID, Z.siteDirID, Z.networkAdsMax, Z.networkThreshold, Prod.productID, (select max(cost) as maxCost from tblProducts),(select min(cost) as minCost from tblProducts) from tblZones Z

JOIN tblProducts Prod on Prod.zoneID = Z.ZoneID
JOIN tblCategories C on Z.categoryID = C.categoryID
JOIN tblParentCategories P on c.parentCategoryID = p.parentCategoryID

WHERE Z.siteDirID = @siteDirID GROUP BY Z.zoneID

GO

Sample data for TblZone

zoneID
userID
websiteName URL description categoryID
siteDirID
networkAdsMax
networkThreshold

1
2
sqlteam
www.sqlteam.com
best site on the net!
38
0
5
2

Sample Data for tblZones

productID
zoneID description
cost
durationDays

1
1
'description'
53.0000
35

2
1
fafsdfasd
234.0000
322

3
1
fdaaadd
134.0000
122

Thanks again for any help. Please excuse the sample sloppy data, i hope its not confusing

Cheers!

mike123

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-09-19 : 01:29:12

what does your tblProducts look like?
See if this works

SELECT Z.zoneID, Z.userID, Z.websiteName, Z.URL, Z.description, Z.categoryID, Z.siteDirID, Z.networkAdsMax, Z.networkThreshold, Prod.productID, (select max(cost) as maxCost from tblProducts where tlbProducts.productid = z.productid),(select min(cost) as minCost from tblProducts where tblProducts.productid = z.productid) from tblZones Z

JOIN tblProducts Prod on Prod.zoneID = Z.ZoneID
JOIN tblCategories C on Z.categoryID = C.categoryID
JOIN tblParentCategories P on c.parentCategoryID = p.parentCategoryID

WHERE Z.siteDirID = @siteDirID GROUP BY Z.zoneID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources