Posted by kevin on 10/26/07 11:49

hello, i have this statment below which runs ok, but i need to add one
more field to it.. I've tried lots of things, but i'm really
struggling now so though i'd ask for some help! anyways..

The KEY is: ItemID
and i need to pull information from a TABLE that's not listed below,
it's table: BinItem
the FIELD i need to pull from it is field: BinName

The problem i have however is that in BinItem, there can be more than
one row for each ItemID
(but not always), and some of the other

So is it possible to add this BinName field to my results where it
only shows the newest row per ItemID?
(this can be linked to item.ItemId)

(if needed there's a DateTimeCreated field which i guess could be
used, but i'm not sure if this is reliable to use, so might be best
without if possible.. OR if there's more than one row use the one
that !='Unspecified' which might be better thinking about it)

anyways.. here's the statement that works (but without the addition
needed above)

SELECT DISTINCT
item.ItemId as ItemID,
item.Code as v_products_model,
item.Name as v_products_description_1,
round((price.Price)*1.159,2) as v_products_price,
item.Weight as v_products_weight,
CAST(item.DateTimeCreated as smalldatetime) as v_date_added,
item.FreeStockQuantity as v_products_quantity
FROM
dbo.ProductGroup prod,
dbo.PLSupplierAccount sup2,
dbo.StockItemSupplier ,
dbo.SearchValue sea,
dbo.SearchValue sec,
dbo.StockItemSearchCatVal stockcat,
dbo.StockItemPrice price,
dbo.StockItem item,
StockItemSupplier sup1,
dbo.BinItem bin
WHERE
item.ItemID = sup1.ItemID
AND item.ItemID = Bin.ItemID
AND item.ItemID = price.ItemID
AND item.ItemID = stockcat.ItemID
AND stockcat.SearchValueID = sea.SearchValueID
AND stockcat.SearchCategoryID = sec.SearchCategoryID
AND sup1.SupplierID = sup2.PLSupplierAccountID
AND item.ProductGroupID = prod.ProductGroupID
Order By 2 DESC /*column number*/


thanks ain advance.. anyhelp would be much appreciated!!!
kev

[Back to original message]