Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Join Problem..........

    Hi,
    I need help. I have one Parent table P1 and a Child Table C1. I have 3 records in table P1 and 9 records in C1 (3 records for each records of P1).

    When I am doing the inner join of these tables i am getting 9 records, where as actually I want only 3 records. I need all 3 rows from P1 and one row each from the C1 against the corresponding rows of P1. Single row from C1 will come from the criteria based on the Date column of the C1 table. Like the row that will be selected from the table C1 for the row from tbale P1 will have the MAX(DATE) value among all the rows in it C1).


    By inner join i am able to extract all the 3 rows where as i need only the row that contains the MAX(DATE).


    Kindly help me in this regard.


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select P1.foo
         , P1.bar
         , M.qux
         , M.date
      from P1  
    inner
      join C1 as M
        on M.flim = P1.flam
       and M.date =
           ( select max(date)
               from C1
              where flim = P1.flam )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Thanks. :-)

    This Will Work. Donno y this din click in my mind.


    Thnaks Once Again


    Rahul Jha

Posting Permissions

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