#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    Philadephia
    Posts
    16
    Rep Power
    0

    Question One to Many Problem


    I need to search 2 tables with some common id, and check a field in the 2nd table to make sure its less than 09/01/2010

    If any records in the 2nd (many table) after greater than that date than the output should NOT contain the data from table1.

    Table1=accounts
    Table2-acctdates

    table2.acctid = table1.acctid

    if table2.acctdate > 09/01/2010 that dont put out this record.

    Any ideas?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    which columns do you want in the output?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    something like

    Code:
    select * 
    from Table1 t1
    inner join (select acctid, max(acctdate) as maxDt
                     from Table2 group by acctid) t2
    on t1.acctid=t2.acctid
    where t2.maxDt < 09/01/2010
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by gk53
    something like...
    yeah, except if he wanted any other columns from table2, which is why i asked what i did

    also, if you actually write 09/01/2010, this equates to zero

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    116
    Rep Power
    9
    actually
    where t2.maxDt < 09/01/2010
    should be where t2.maxDt < '09/01/2010'

    I can only guess with you what it means
    and sure if he need more data from table2 it could change query

IMN logo majestic logo threadwatch logo seochat tools logo