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

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

[Back to original message]