Posted by kevin on 10/29/07 10:43

On Oct 26, 4:37 pm, jhofm...@googlemail.com wrote:
> On Oct 26, 5:33 pm, jhofm...@googlemail.com wrote:
>
>
>
>
>
> > On Oct 26, 12:49 pm, 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 ain advance.. anyhelp would be much appreciated!!!
> > > kev
>
> > Hi Kev,
>
> > There are a few ways to do this. In SQL2005 I'd probably use a CTE -
> > something like:
> > WITH
> > LatestBinItem AS
> > (
> > SELECT ItemID, BinName
> > FROM BinItem
> > WHERE BinName != 'Unspecified'
> > )
> > SELECT
> > ...
> > , lbi.BinName
> > FROM
> > ...
> > , LatestBinItem lbi
> > WHERE
> > ...
> > AND item.ItemID = lbi.ItemID
> > ORDER BY 2 DESC
>
> > In SQL2000 you'd need to use a derived table:
> > SELECT
> > ...
> > , lbi.BinName
> > FROM
> > ...
> > , (
> > SELECT ItemID, BinName
> > FROM BinItem
> > WHERE BinName != 'Unspecified'
> > ) AS lbi
> > WHERE
> > ...
> > AND item.ItemID = lbi.ItemID
> > ORDER BY 2 DESC
>
> > This method assumes that there is only 1 row in the BinItem table with
> > a name != 'Unspecified' though. If there's more than 1 row I'd
> > recommend using the date field or the BinName primary key (assuming
> > there is one!) You'd then change your CTE or derived table slightly
> > to look something like:
> > WITH
> > LatestBinItemDate AS
> > (
> > SELECT ItemID, MAX(DateTimeCreated)
> > FROM BinItem
> > GROUP BY ItemID
> > )
> > LatestBinItem AS
> > (
> > SELECT ItemID, BinName
> > FROM BinItem bi
> > INNER JOIN LatestBinItemDate lbid
> > ON lbid.ItemID = bi.ItemID
> > AND lbid.DateTimeCreated = bi.DateTimeCreated
> > )
>
> > I'd also change your statement to use explicit JOIN's instead of
> > implicit ones. Mostly because it makes your script more readable.
>
> > Good luck!
> > J- Hide quoted text -
>
> > - Show quoted text -
>
> Oops - I left out a comma
> WITH
> LatestBinItemDate AS
> (
> SELECT ItemID, MAX(DateTimeCreated)
> FROM BinItem
> GROUP BY ItemID
> )
> , LatestBinItem AS -- forgot comma here
> (
> SELECT ItemID, BinName
> FROM BinItem bi
> INNER JOIN LatestBinItemDate lbid
> ON lbid.ItemID = bi.ItemID
> AND lbid.DateTimeCreated = bi.DateTimeCreated
> )
>
> J- Hide quoted text -
>
> - Show quoted text -

Thanks for your help J, it's very much appreciated. i'll give it a
bash and post up the result if i find it!! :)

[Back to original message]