| Author |
 Topic  |
|
|
masterslave
Starting Member
22 Posts |
 Posted - 01/15/2008 : 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
Sweden
29138 Posts |
Posted - 01/15/2008 : 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
India
47099 Posts |
Posted - 01/15/2008 : 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
|
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 01/15/2008 : 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
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 01/15/2008 : 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
India
47099 Posts |
Posted - 01/16/2008 : 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. |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 01/16/2008 : 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
India
47099 Posts |
Posted - 01/16/2008 : 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? |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 01/16/2008 : 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
India
47099 Posts |
Posted - 01/17/2008 : 12:16:05
|
| ok.then its fine. Did the soln gave you desired result? |
|
|
|
masterslave
Starting Member
22 Posts |
Posted - 01/22/2008 : 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
India
47099 Posts |
Posted - 01/22/2008 : 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 - 01/24/2008 : 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 |
|
|
| |
Topic |
|
Help with MIN(Cost)
