CASE count wierd join needed maybe? just one more field needed!

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

Re: CASE count wierd join needed maybe? just one more field needed!

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

Re: CASE count wierd join needed maybe? just one more field needed!

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

Re: CASE count wierd join needed maybe? just one more field needed!

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.

Re: CASE count wierd join needed maybe? just one more field needed!

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!! :)

Re: CASE count wierd join needed maybe? just one more field needed!

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
>From ...
....
dbo.kev_bin kbin
....
WHERE ..
item.ItemID = kbin.ItemID
.....

and this runs in about 20 seconds :D

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

Re: CASE count wierd join needed maybe? just one more field needed!

(kevin@nu-urbanmusic.co.uk) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: CASE count wierd join needed maybe? just one more field needed!

On Nov 6, 10:31 pm, Erland Sommarskog wrote:
> (ke...@nu-urbanmusic.co.uk) writes:
> > 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.
>
> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi Kev,

Glad I could help :)

J