Finding difference with Join

[burf]

I feel this would be resolved by using join but can't seem to figure out how

my table looks like

user - date
1 - 1-march-08
1 - 5-march-08
1 - 15-march-08
2 - 16-march-08
3 - 15-march-08
3 - 18-march-08
4 - 11-march-08
4 - 21-march-08
5 - 15-march-08

i need to find out all those users who have date more than 14-march but NOT before that

the result set would look like:-

user - date
2 - 16-march-08
3 - 15-march-08
3 - 18-march-08
5 - 15-march-08

[skhanal]

You don't need a join, use WHERE clause to filter out date < 14-march.

[burf]

it doesn't generate the intended result

[SDas]

burf, try this:

SELECT USER_COL, DATE_COL
FROM table-name
WHERE USER_COL NOT IN (SELECT USER_COL
                       FROM table-name
                       WHERE DATE_COL < '14-MARCH-08'
                      )

[skhanal]

Ok I think you want to eliminate users with date > 14 mar if they are also in < 14. You have to do

select * from table as a
where a.date > 14 mar
and not exists (select * from table as b join a on a.user=b.user
where date < 14 mar)

[jilani]

select * from table where sta_date> '14-march-08'

the result for the above is

US STA_DATE
1 15-MAR-08
2 16-MAR-08
3 15-MAR-08
3 18-MAR-08
4 21-MAR-08
5 15-MAR-08

[SDas]

jilani, That isn't exactly what burf wanted. The requirement is the user has a row after 14 Mar but only if that same user does not have another row before that date. In the example data, user 1 has 3 rows:

1 - 1-march-08
1 - 5-march-08
1 - 15-march-08

While one of the rows is > 14 Mar, since there are also rows in the table before 14 Mar, this would eliminate user 1 from being returned.

[fsdba]

You are correct in thinking the results can be obtained by using a join. Here is the solution for the problem using a left join. There are two fields in table (user, and date)

select a.*
from
(select *
from dbo.tbl
where date > '3/14/2011') a
left join
(select *
from dbo.tbl
where date <= '3/14/2011') b
on a.username = b.username
where b.username is null

Results:
2 2011-03-16 00:00:00.000
3 2011-03-15 00:00:00.000
3 2011-03-18 00:00:00.000
5 2011-03-15 00:00:00.000

[shubho12003]

You dont need a join... a simple subquery should work fine.


select * from <table> a
where date > "14-Mar-08"
and not exists ( select 1 from <table> b where a.user=b.user and date <= "14-Mar-08")