# Miscellaneous > SQL Scripts >  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")

----------

