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 cataloguethat are linked to base products and then all costings forthose products in LatestSupplierItemView. Majority of productshave only one record which is in LatestSupplierItemView, but some products are actually supplied by several suppliers soat the moment it would get the first one it finds whereas itneeds to get out of four supplier items connected to the base product the one with minimum cost! I'm not sure how to goabout it, the WHERE clause or in the JOIN... Any help much appreciated!CREATE VIEW dbo.CatalogueItemFullViewASSELECT 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" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-15 : 03:24:51
|
Change like this:-CREATE VIEW dbo.CatalogueItemFullViewASSELECT CatalogueItem.*,dbo.fn_Round(SupplierItem.RRP) AS [RRP],dbo.fn_Round(tmp.MinCost) AS [OCCost1]FROM CatalogueItem INNER JOINCatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOINBaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOINLatestSupplierItemView AS SupplierItem ON (BaseProduct.BaseProductId = SupplierItem.BaseProductId) INNER JOINSupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId) INNER JOIN(SELECT BaseProductId,MIN(OCCost) AS 'MinCost' FROM LatestSupplierItemView GROUP BY BaseProductId)tmpON tmp.BaseProductId =SupplierItem.BaseProductId |
|
|
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. |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-16 : 01:20:55
|
Try this:-CREATE VIEW dbo.CatalogueItemFullViewASSELECT CatalogueItem.*,dbo.fn_Round(tmp.RRP) AS [RRP],dbo.fn_Round(tmp.OCCost) AS [OCCost1]FROM CatalogueItem INNER JOINCatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOINBaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOIN(SELECT SupplierItem.BaseProductId,MIN(SupplierItem.RRP) AS RRP,MIN(SupplierItem.OCCost) AS OCCostFROMLatestSupplierItemView AS SupplierItem INNER JOINSupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId))tmpON tmp.BaseProductId= BaseProduct.BaseProductId Also i doubt whether you really need the join in blue. |
|
|
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? |
|
|
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 amendmentCREATE VIEW dbo.CatalogueItemFullViewASSELECT CatalogueItem.*,dbo.fn_Round(tmp.RRP) AS [RRP],dbo.fn_Round(tmp.OCCost) AS [OCCost1]FROM CatalogueItem INNER JOINCatalogueProduct ON (CatalogueItem.CatalogueProductId = CatalogueProduct.CatalogueProductId) INNER JOINBaseProduct ON (CatalogueItem.BaseProductId = BaseProduct.BaseProductId) INNER JOIN(SELECT SupplierItem.BaseProductId,MIN(SupplierItem.RRP) AS RRP,MIN(SupplierItem.OCCost) AS OCCostFROMLatestSupplierItemView AS SupplierItem INNER JOINSupplierProduct ON (SupplierProduct.SupplierProductId = SupplierItem.SupplierProductId)GROUP BY SupplierItem.BaseProductId)tmpON tmp.BaseProductId= BaseProduct.BaseProductIdas i asked before do u really require code in red? |
|
|
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 |
|
|
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? |
|
|
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 |
|
|
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 |
|
|
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)tmpON tmp.BaseProductId = SupplierItem.BaseProductIdAND tmp.MinCost= SupplierItem.OCCost |
|
|
|