Results 1 to 9 of 9

Thread: Finding difference with Join

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Finding difference with Join

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You don't need a join, use WHERE clause to filter out date < 14-march.

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    it doesn't generate the intended result

  4. #4
    Join Date
    Apr 2009
    Posts
    86
    burf, try this:
    Code:
    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'
                          )

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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)

  6. #6
    Join Date
    Jan 2011
    Posts
    2

    it is very simple querry

    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

  7. #7
    Join Date
    Apr 2009
    Posts
    86
    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.

  8. #8
    Join Date
    Feb 2011
    Posts
    1

    Results by using Join

    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

  9. #9
    Join Date
    Mar 2011
    Posts
    2

    Smile

    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")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •