Heirarchy - 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)

Heirarchy

Author

Topic

havab
Starting Member

3 Posts

Posted - 2009-12-21 : 13:06:13

Hi,
I'm using MS SQL Server 2005.

I have 3 tables (the abbreviated details of which are below). Basically, "Product" can have one or more "Product Items". A "Product item" can have one or more "product item boxes". Each product item box has its own shipping cost.

Basically the price of a "product item" is its cost + the total of the shipping cost for each of its boxes.

I'm trying to find the minumum and maximum price for a "product" in a given catalog section.

Here is the query I'm using. Its kind of a mess, and not really giving me the result I was expecting:

SELECT DISTINCT
tbl_Products.row_id, tbl_Products.name, tbl_Products.description, tbl_Products.photo, tbl_Product_Items_1.margin,
(SELECT MIN(cost) AS Expr1 FROM tbl_Product_Items WHERE (parent_product_id = tbl_Products.row_id)) AS MIN_PRICE,
(SELECT MAX(cost) AS Expr1 FROM tbl_Product_Items AS tbl_Product_Items_2 WHERE (parent_product_id = tbl_Products.row_id)) AS MAX_PRICE,
(SELECT SUM(avg_shipping_cost) AS Expr1 FROM tbl_Product_Item_Boxes AS PIB WHERE (product_item_id = tbl_Product_Items_1.row_id)) AS SHIP_COST
FROM tbl_Products INNER JOIN
tbl_Product_Items AS tbl_Product_Items_1 ON tbl_Products.row_id = tbl_Product_Items_1.parent_product_id
WHERE (tbl_Products.catalog_section_id = 18) AND (tbl_Products.orderable = 1)
ORDER BY tbl_Products.name

tbl_Products
---------------------
row_id
name
description
catalog_section_id
orderable

tblProductItem
---------------------
row_id
parent_product_id
name
cost

tblProductItemBox
---------------------
row_id
product_item_id
avg_shipping_cost

Hopefully this describes the issue, I'd appreciate any help. It may be obvious that my SQL skills need work.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-21 : 13:20:25

SELECT DISTINCT
tbl_Products.row_id,
tbl_Products.name,
tbl_Products.description,
tbl_Products.photo,
tbl_Product_Items_1.margin,
b.minPrice AS MIN_PRICE,
b.maxPrice AS MAX_PRICE,
avg_shipping_cost AS SHIP_COST
FROM tbl_Products prd
INNER JOIN
(
Select product_item_id,
min(cost) as minPrice,
max(cost) as maxPrice
From tbl_product_item
group by product_item_id
) b ON b.Row_id = prd.Row_id
WHERE (tbl_Products.catalog_section_id = 18) AND (tbl_Products.orderable = 1)
ORDER BY tbl_Products.name

Try this. Also your summing of the avg cost seems suspect, you may want to check it.

havab
Starting Member

3 Posts

Posted - 2009-12-21 : 14:33:53

thanks for the quick feedback.
i think the key issue I'm having is with the sum of the average cost.

it probably wasn't clear in my initial description, but the average cost is a column in the tblProductItemBox table, which I don't see in the query you provided.

not sure if you are able to update it with that info, that's really the part I'm having trouble with (getting the total of all the avg_shipping_cost columns for a "Product Item".)

thanks again for the help!

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-21 : 14:43:42

I kind of removed some of the code you had for Avg cost, I was confused as to why you might want to sum the averages, doesn't each product only have 1 average price? Or are there multiple avg prices for a product based on warehouse? If so, you may just want to use AVG(avg_shipping_cost) AS SHIP_COST but you will need to group by all the other columns. If you need the sum then SUM(avg_shipping_cost) AS SHIP_COST should work.

I left the avg ship cost in the query I made, but never aggregated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources