Posted by --CELKO-- on 10/27/07 14:42

On Oct 26, 6:49 am, ke...@nu-urbanmusic.co.uk wrote:
> 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 in advance.. any help would be much appreciated!!!
> kev

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Sample data is also a good idea,
along with clear specifications. It is very hard to debug code when
you do not let us see it.

What you did show is vague. Price should be an attribute of an item
and not an entity by itself. There is no such thing as a
"category_id" -- an attribute can be one or the other but not both.
I have a bin_item and a bin_name, but no entity called Bins. This
looks like a lot of attribute splitting leaving you with a messy
schema.

[Back to original message]