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

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0

    Angry Select statement base on data


    hi guys...need a little help..
    i have two tables containing ....
    POST TABLE
    idpost
    idemployee
    idpostlist
    post joining data
    post leaving date

    POSTLIST TABLE

    idpostlist
    postname
    ....
    ...
    i want to select the current designation(post) base on the latest date.....because one employee can be posted to different post and the latest post will determine his current designation
    can anyone help me.........plz
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by zorrs
    i want to select the current designation(post) base on the latest date.....
    based on "post joining data"?

    presumably this is a date column?

    what's the actual column name?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    sorry....for not giving the actual column name...

    POST TABLE
    idpost(interger)(PK)
    idemployee(Fk)
    idpostlist(Fk)
    P_DOJ(date)
    P_DOL

    POSTLIST TABLE
    idpostlist(Integer)(PK)
    postname(FK)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by zorrs
    sorry....for not giving the actual column name...
    Code:
    SELECT p.idemployee
         , p.p_doj
         , pl.postname
      FROM ( SELECT idemployee
                  , MAX(p_doj) AS latest
               FROM post
             GROUP
                 BY idemployee ) AS m
    INNER
      JOIN post AS p
        ON p.idemployee = m.idemployee
       AND p.p_doj = m.latest
    INNER
      JOIN postlist AS pl
        ON pl.idpostlist = p.idpostlist
    by the way, which database system is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    thanks alot...let me try it out...
    database is MYSQL V.5.0.11
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by zorrs
    database is MYSQL V.5.0.11
    there's a special forum for mysql, i'll move your thread over
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo