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

On Oct 26, 12:49 pm, ke... (AT) nu-urbanmusic (DOT) co.uk wrote:
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

On Oct 26, 5:33 pm, jhofm... (AT) googlemail (DOT) com wrote:

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

On Oct 26, 6:49 am, ke... (AT) nu-urbanmusic (DOT) co.uk wrote:
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.

On Oct 26, 4:37 pm, jhofm... (AT) googlemail (DOT) com wrote:
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!!

Right, I used your handy answers to get a solution that worked.. Your
last method was the one to use but i left it running for 3 hours and
it still hadn't returned any results! (and it's a stupidly high spec
server with pretty much just sql on there)

anyway.. so in SQL Server managment studio i found the "Views" section
(sorta like tempory tables i presume) and popped this into a new one
call kev_bin.

WITH Bin1 AS (SELECT ItemID, MAX(DateTimeCreated) AS MDate
FROM dbo.BinItem AS Bin1
GROUP BY ItemID)
SELECT Bin2.ItemID, Bin2.BinName
FROM dbo.BinItem AS Bin2 INNER JOIN
Bin1 AS Bin1 ON Bin2.ItemID = Bin1.ItemID
AND Bin2.DateTimeCreated = Bin1.MDate


I could then go back to my orignal SQL and use the following to grap
the data from the newly created table kev_bin above:

Select ... .. ...
kbin.BinName as location
dbo.kev_bin kbin
....
WHERE ..
item.ItemID = kbin.ItemID
.....

and this runs in about 20 seconds

thanks again for all your help, i couldn't have done it without!

(kevin (AT) nu-urbanmusic (DOT) co.uk) writes:
Views are just a logical concept. Except for indexed view, they are not
materialised, and they are expanded so that the optimizer works with the
expanded query. That is, essentiall a macro.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Nov 6, 10:31 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Hi Kev,

Glad I could help

J