Posted by jhofmeyr on 10/26/07 16:37

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

[Back to original message]