please help

[FALCONSEYE]

i have two queries as follows

SELECT users.userid
FROM products, userhist a, users
WHERE users.userid = a.userid
AND a.productid = products.productid
AND eventid = 3 -- successfull orders
AND a.datemodified between '6/1/2005' and '6/15/2005'
AND a.userid not in (SELECT MAX(datemodified) from userhist b where statusid in(3,2) and b.userid = a.userid
and eventid = 1)
AND productname = 'Learn Windows®'
which returns
userid
59684
59715

the second query is
SELECT products.productName, emailAddress,
(SELECT MAX(datemodified) from userhist b where statusid in(3,2) and b.userid = a.userid
and eventid = 1) as datemod
, users.userid, eventid, statusid
FROM products, userhist a, users
WHERE users.userid = a.userid
AND a.productid = products.productid
AND eventid = 3 -- successfull orders
AND a.datemodified between '6/1/2005' and '6/15/2005'
AND productname = 'Learn Windows®'

which returns
Learn Windows® test@test1.com NULL 59683
Learn Windows® test@test1.com 2005-06-09 15:03:52.200 59684
Learn Windows® test@test1.com NULL 59694
Learn Windows® test@test1.com NULL 59704
Learn Windows® test@test1.com NULL 59705
Learn Windows® test@test1.com NULL 59714
Learn Windows® test@test1.com 2005-06-09 14:59:12.467 59715
Learn Windows® test@test1.com NULL 59717
Learn Windows® test@test1.com NULL 59718
Learn Windows® test@test1.com NULL 59724
Learn Windows® test@test1.com NULL 59725
Learn Windows® test@test1.com NULL 59734
Learn Windows® test@test1.com NULL 59739
Learn Windows® test@test1.com NULL 59741
Learn Windows® test@test1.com NULL 59749
Learn Windows® test@test1.com NULL 59767


all i want is not to return two users 59684, 59715
from the second query. what am i doing wrong ?
as you will realize the second query returns none null values for these two users. thanks for the help in advance

 

[mercwrought]

from a quick glance you are not limiting yor userideas like in the first query

 SELECT products.productName, emailAddress, (SELECT MAX(datemodified) from userhist b where statusid in(3,2) and b.userid = a.userid and eventid = 1) as datemod, users.userid, eventid, statusid
    FROM   products, userhist a, users
    WHERE  users.userid = a.userid
    AND    a.productid = products.productid
    AND eventid = 3 -- successfull orders
    AND     a.datemodified between '6/1/2005' and '6/15/2005'
    AND    productname =  'Learn Windows®'
    AND   a.userid not in (SELECT MAX(datemodified) from userhist b where statusid in(3,2) and b.userid = a.userid  and eventid = 1)

 

[FALCONSEYE]

by the way Merc, the query you wrote the results that i dont want. meaning person1 and person2 should not be there, instead all the others should. how can i modify the query so it will do the right thing?
thanks a million

 

[mercwrought]

I'm so sorry I misread your post. you can delete the not in the last where clause. see how that works.

sorry bout that

 

[FALCONSEYE]

MercWrought, I tried and removed the 'NOT' but now it returns nothing instead i should have other 14 people

 

[Catadmin]

If I'm reading your post right, you want all users that are not users # 59684 and # 59715.

This should be able to be done using your above query and tacking "And userid <> 59684 and userid <> 59715" or "And UserID not in (59684,59715)" on the end of your query.

I am a little confused, though. Is it Merc's query that isn't returning the other users or is it both Merc's query and your second query that aren't returning other user info?

If it's just Merc's query (and my assumption above is correct), this should resolve your problem. If it's both queries, then your WHERE statement is filtering on conditions that don't exist for the other users.




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mercwrought]

sorry im im in the middle of a project right now perhaps this will work

SELECT products.productName, emailAddress,
(SELECT MAX(datemodified) from userhist b where statusid in(3,2) and b.userid = a.userid
and eventid = 1) as datemod
, users.userid, eventid, statusid
FROM   products, userhist a, users
WHERE eventid = 3 -- successfull orders
AND     a.datemodified between '6/1/2005' and '6/15/2005'
AND    productname =  'Learn Windows®'
AND (SELECT MAX(datemodified) from userhist b where statusid in(3,2) and b.userid = a.userid) is NULL

its ulgy but it may work