Multiple Table Problem

Hi and Thanks to all in advance for help,
I have 2 tables. What we want to do is grab the columns listed and then
if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
items that have ONHAND(int) in the other table (STOCK) is greater than
0.
SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
KEYWORDITEMS
WHERE
STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
AND
STOCK.WH = 1
AND
(Here is the confusion)
What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
I attempted to use CASE statements but it was not working because it
wanted a result if the condition was false. I am sure I didn't do it
right though.
Other things I am trying to do:
CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only
the returned result that is to become the new table) = "BEST DEAL" ELSE
''.
I would like to drop all STOCK columns after it is returned except
[DESCRIPTION] and [STNS]
Thanks again for your help.
Kelly"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
> the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
>
Try:
AND (ProductGroupNumber < 4999 OR Stock.OnHand >0)
JBK|||How are the two tables related? What does their DDL (create table) statement
s
look like?
Thomas
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
the
> PRODUCTGROUP(int) column is larger than 4999 we only want to grab items th
at
> have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD, STOCK.ITEMNUMB
ER,
> STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK, KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it wante
d a
> result if the condition was false. I am sure I didn't do it right though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only the
> returned result that is to become the new table) = "BEST DEAL" ELSE ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>|||I don't understand exactly what you are trying to do. Part of my confusion
is caused by your incomplete select statement. Take a look at this example
that I wrote and see if it gets you pointed in the right direction.
USE Northwind
GO
SELECT A.OrderID, A.CustomerID, A.EmployeeID,
A.OrderDate, A.RequiredDate, A.ShippedDate,
'EmployeeORShip' = CASE WHEN A.ShippedDate IS NULL THEN 'emp-' +
C.FirstName + ' ' + C.LastName ELSE 'shp-' + B.ContactName END
FROM Orders A
JOIN Customers B ON A.CustomerID = B.CustomerID
JOIN Employees C ON A.EmployeeID = C.EmployeeID
Keith
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
> the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it
> wanted a result if the condition was false. I am sure I didn't do it right
> though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only the
> returned result that is to become the new table) = "BEST DEAL" ELSE ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>|||Try,
SELECT
PRODUCTGROUPNUMBER,
ITEMNO,
STOCK.[DESCRIPTION],
KYWD,
STOCK.ITEMNUMBER,
STOCK.WH,
STOCK.ONHAND,
STOCK.STNS
FROM
STOCK
inner join
KEYWORDITEMS
on STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO AND STOCK.WH = 1
WHERE
(STOCK.ONHAND > 0 and PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999)
or
PRODUCTGROUP.PRODUCTGROUPNUMBER <= 4999
AMB
"scorpion53061" wrote:

> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then
> if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than
> 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it
> wanted a result if the condition was false. I am sure I didn't do it
> right though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only
> the returned result that is to become the new table) = "BEST DEAL" ELSE
> ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>
>|||I love what you did here though I have run the query for 10 minutes and
it is still running.
Here is what I have now with the corrected table names and fields....
SELECT
DISTINCT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM
STOCK2
inner join
KEYWORDITEMS11
on STOCK2.NUMBER = KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE
(STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
or
KEYWordITEMS11.PRODUCTGROUP <= 4999
ORDER By PRODUCTGROUP, ITEMNO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message news:AlejandroMesa@.discussions.microsoft.com:
> Try,
> SELECT
> PRODUCTGROUPNUMBER,
> ITEMNO,
> STOCK.[DESCRIPTION],
> KYWD,
> STOCK.ITEMNUMBER,
> STOCK.WH,
> STOCK.ONHAND,
> STOCK.STNS
> FROM
> STOCK
> inner join
> KEYWORDITEMS
> on STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO AND STOCK.WH = 1
> WHERE
> (STOCK.ONHAND > 0 and PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999)
> or
> PRODUCTGROUP.PRODUCTGROUPNUMBER <= 4999
>
> AMB
>
> "scorpion53061" wrote:
>|||You might get a better perf if you convert your "DISTINCT" and "OR" to
"UNION" query.
e.g.
SELECT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM STOCK2 inner join KEYWORDITEMS11 on STOCK2.NUMBER =
KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE (STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
UNION
SELECT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM STOCK2 inner join KEYWORDITEMS11 on STOCK2.NUMBER =
KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE KEYWordITEMS11.PRODUCTGROUP <= 4999
ORDER By PRODUCTGROUP, ITEMNO
-oj
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:%23BTFhOBTFHA.2872@.TK2MSFTNGP14.phx.gbl...
>I love what you did here though I have run the query for 10 minutes and it
>is still running.
> Here is what I have now with the corrected table names and fields....
> SELECT
> DISTINCT
> PRODUCTGROUP,
> ITEMNO,
> STOCK2.[DESCRIPTION],
> KYWD,
> STOCK2.NUMBER,
> STOCK2.WH,
> STOCK2.ONHAND,
> STOCK2.STNS
> FROM
> STOCK2
> inner join
> KEYWORDITEMS11
> on STOCK2.NUMBER = KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
> WHERE
> (STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
> or
> KEYWordITEMS11.PRODUCTGROUP <= 4999
> ORDER By PRODUCTGROUP, ITEMNO
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:AlejandroMesa@.discussions.microsoft.com:
>