1

I have table like this:

CreateDate  |  UserID
2012-01-1   |     1
2012-01-10  |     2
2012-01-20  |     3
2012-02-2   |     4
2012-02-11  |     1
2012-02-22  |     2
2012-03-5   |     3
2012-03-13  |     4
2012-03-17  |     5

I need the query to show UserID which created after 1 February 2013 and not exist in database befor 1 February 2013

From the above example the result must be: 

CreateDate  |  UserID
2012-02-2   |     4
2012-03-13  |     4
2012-03-17  |     5

Can it resolved only in single query without Stored Procedure?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Teddy
  • 37
  • 7

2 Answers2

1

You can use a subquery which separately gets the UserID which exist before Feb. 2, 2013 and the result of the subquery is then joined back on the table itself using LEFT JOIN.

SELECT  a.*
FROM    tableName a
        LEFT JOIN 
        (
            SELECT  UserID
            FROM    tableName
            WHERE   CreateDate < '2013-02-01'
        ) b ON a.userID = b.userID
WHERE   a.CreateDate > '2013-02-01' AND
        b.userID IS NULL

for faster performance, add an INDEX on column userID.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Yess.... this work to..... hmmm now which one is better query for a table with lots of data. Should I use Left Join or SubQuery in WHERE ? – Teddy Feb 19 '13 at 09:47
  • If you have properly define index on the table then you don't have to worry about performance. – John Woo Feb 19 '13 at 09:49
0

This is one way to do it:

select 
  CreateDate,
  UserID
from Users
where CreateDate >= '2012-02-01'
and UserId not in (
  select UserId
  from Users
  where CreateDate < '2012-02-01'
)

SqlFiddle link: http://www.sqlfiddle.com/#!2/7efae/2

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
  • Ahhh... Damn... Stupid Brain...... Thank you for your help....thats the answer I need...... – Teddy Feb 19 '13 at 09:41