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
 SQL Server Development (2000)
 Help with MIN(Cost)

Author  Topic 

masterslave
Starting Member

22 Posts

Posted - 2008-01-15 : 01:20:28
I've got a view where I'm getting all products in catalogue
that are linked to base products and then all costings for
those products in LatestSupplierItemView. Majority of products
have only one record which is in LatestSupplierItemView, but
some products are actually supplied by several suppliers so
at the moment it would get the first one it finds whereas it
needs to get out of four supplier items connected to the
base product the one with minimum cost! I'm not sure how to go
about it, the WHERE clause or in the JOIN... Any help much
appreciated!


CREATE VIEW dbo.CatalogueItemFullView
AS
SELECT CatalogueItem.*,
dbo.fn_Round(SupplierItem.RRP) AS [RRP],
dbo.fn_Round(SupplierItem.OCCost) AS [OCCost1]
FROM CatalogueItem INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOIN
LatestSupplierItemView AS SupplierItem ON (BaseProduct.BaseProductId = SupplierItem.BaseProductId) INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 03:23:01
You could turn the view into a table-valued function instead.
This also has the advantage that you can pass parameters to the function.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 03:24:51
Change like this:-
CREATE VIEW dbo.CatalogueItemFullView
AS
SELECT CatalogueItem.*,
dbo.fn_Round(SupplierItem.RRP) AS [RRP],
dbo.fn_Round(tmp.MinCost) AS [OCCost1]
FROM CatalogueItem INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOIN
LatestSupplierItemView AS SupplierItem ON (BaseProduct.BaseProductId = SupplierItem.BaseProductId) INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId) INNER JOIN
(SELECT BaseProductId,MIN(OCCost) AS 'MinCost' FROM LatestSupplierItemView GROUP BY BaseProductId)tmp
ON tmp.BaseProductId =SupplierItem.BaseProductId

Go to Top of Page

masterslave
Starting Member

22 Posts

Posted - 2008-01-15 : 21:44:16
thanks visakh16, but the query then takes a really long time...
i was just trying to do it like that:

FROM CatalogueItem
INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId)
INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId)
INNER JOIN
(SELECT TOP 1 * FROM LatestSupplierItemView LSIV WHERE LSIV.BaseProductId = BaseProduct.BaseProductId ORDER BY OCCost ASC) SupplierItem
ON SupplierItem.BaseProductId = SupplierItem.BaseProductId
INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)

but it gives me an error message that says:
The column prefix 'BaseProduct' does not match with a table name or alias name used in the query.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-15 : 22:36:43
INNER JOIN
(SELECT TOP 1 * FROM LatestSupplierItemView LSIV WHERE LSIV.BaseProductId = BaseProduct.BaseProductId ORDER BY OCCost ASC)

That is where the error is. The table Base Product is not in the FROM statement.



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 01:20:55
Try this:-

CREATE VIEW dbo.CatalogueItemFullView
AS
SELECT CatalogueItem.*,
dbo.fn_Round(tmp.RRP) AS [RRP],
dbo.fn_Round(tmp.OCCost) AS [OCCost1]
FROM CatalogueItem INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOIN
(SELECT SupplierItem.BaseProductId,MIN(SupplierItem.RRP) AS RRP,MIN(SupplierItem.OCCost) AS OCCost
FROM
LatestSupplierItemView AS SupplierItem INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)
)tmp
ON tmp.BaseProductId= BaseProduct.BaseProductId


Also i doubt whether you really need the join in blue.
Go to Top of Page

masterslave
Starting Member

22 Posts

Posted - 2008-01-16 : 08:15:57
dataguru1971, but how can I link to the base product table, i thought it should work because i do inner join from it?

visakh16, would that pick out items "grouped" by the base product id as it is needed though?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 08:58:57
quote:
Originally posted by masterslave

dataguru1971, but how can I link to the base product table, i thought it should work because i do inner join from it?

visakh16, would that pick out items "grouped" by the base product id as it is needed though?


sorry slight amendment

CREATE VIEW dbo.CatalogueItemFullView
AS
SELECT CatalogueItem.*,
dbo.fn_Round(tmp.RRP) AS [RRP],
dbo.fn_Round(tmp.OCCost) AS [OCCost1]
FROM CatalogueItem INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOIN
(SELECT SupplierItem.BaseProductId,MIN(SupplierItem.RRP) AS RRP,MIN(SupplierItem.OCCost) AS OCCost
FROM
LatestSupplierItemView AS SupplierItem INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)
GROUP BY SupplierItem.BaseProductId)tmp
ON tmp.BaseProductId= BaseProduct.BaseProductId

as i asked before do u really require code in red?
Go to Top of Page

masterslave
Starting Member

22 Posts

Posted - 2008-01-16 : 16:10:08
visakh16, join to supplierproduct i do need because i'm also reading reading descriptions for supplieritem, it's just that i took those out of select for better readability
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-17 : 12:16:05
ok.then its fine. Did the soln gave you desired result?
Go to Top of Page

masterslave
Starting Member

22 Posts

Posted - 2008-01-22 : 20:15:51
Not really! Because I'm first joining to LatestSupplierItemView view and only then joining to LatestSupplierItemView with minimum cost condition it selects many more items than actually required! What do you think I can do to limit the output only to second join of LatestSupplierItemView with the required condition?

FROM
CatalogueItem
INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId)
INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId)
INNER JOIN
LatestSupplierItemView AS SupplierItem ON (BaseProduct.BaseProductId = SupplierItem.BaseProductId)
INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)
INNER JOIN
(SELECT BaseProductId, MIN(OCCost) AS 'MinCost' FROM LatestSupplierItemView GROUP BY BaseProductId)tmp
ON tmp.BaseProductId = SupplierItem.BaseProductId
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-22 : 22:52:19
quote:
Originally posted by masterslave

Not really! Because I'm first joining to LatestSupplierItemView view and only then joining to LatestSupplierItemView with minimum cost condition it selects many more items than actually required! What do you think I can do to limit the output only to second join of LatestSupplierItemView with the required condition?

FROM
CatalogueItem
INNER JOIN
CatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId)
INNER JOIN
BaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId)
INNER JOIN
LatestSupplierItemView AS SupplierItem ON (BaseProduct.BaseProductId = SupplierItem.BaseProductId)
INNER JOIN
SupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)
INNER JOIN
(SELECT BaseProductId, MIN(OCCost) AS 'MinCost' FROM LatestSupplierItemView GROUP BY BaseProductId)tmp
ON tmp.BaseProductId = SupplierItem.BaseProductId
AND tmp.MinCost= SupplierItem.OCCost



try like this and see
Go to Top of Page

masterslave
Starting Member

22 Posts

Posted - 2008-01-24 : 00:10:39
Great idea, visakh16! It's almost there, but it's just for some reason cuts down of results, like it's not finding some matches in the last INNER JOIN:

INNER JOIN
(SELECT BaseProductId, MIN(OCCost) AS 'MinCost' FROM LatestSupplierItemView GROUP BY BaseProductId)tmp
ON tmp.BaseProductId = SupplierItem.BaseProductId
AND tmp.MinCost= SupplierItem.OCCost
Go to Top of Page
   

- Advertisement -