SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help with MIN(Cost)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterslave
Starting Member

22 Posts

Posted - 01/15/2008 :  01:20:28  Show Profile  Reply with Quote
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

Sweden
30111 Posts

Posted - 01/15/2008 :  03:23:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 01/15/2008 :  03:24:51  Show Profile  Reply with Quote
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 - 01/15/2008 :  21:44:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 01/15/2008 :  22:36:43  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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

India
52309 Posts

Posted - 01/16/2008 :  01:20:55  Show Profile  Reply with Quote
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 - 01/16/2008 :  08:15:57  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/16/2008 :  08:58:57  Show Profile  Reply with Quote
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 - 01/16/2008 :  16:10:08  Show Profile  Reply with Quote
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

India
52309 Posts

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

masterslave
Starting Member

22 Posts

Posted - 01/22/2008 :  20:15:51  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/22/2008 :  22:52:19  Show Profile  Reply with Quote
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 - 01/24/2008 :  00:10:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000