Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: joining table on on last entered record

    Dear All,

    What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

    I'd like to select every record in table A but only joining the last relevant record from table B. So:

    Table A:

    A1 Prj1
    A2 Prj2

    Table B:

    B1 A1 23/12/2005
    B2 A1 26/12/2005
    B3 A1 2/1/2007
    B4 A2 25/12/2006
    B5 A2 1/1/2007

    So I'd like to list using the most efficient way this:

    A1 Prj1 B3 2/1/2007
    A2 Prj2 B5 1/1/2007

    I'm assuming this is NOT the most efficient way:

    select A, (select top 1 date from B orderBy ...)

    Any suggestions?

  2. #2
    Join Date
    Mar 2005
    Posts
    92
    Maybe this:

    select A, B
    from A
    inner join A on A.Aid = B.Aid
    where B.date = (select max(B.date) from B where...) ...


    this works faster but is there a better way? (I'm sure there is)

    Anyone?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I use this:
    Code:
    select	A,
    	B
    from	A
    	inner join --LastRecords
    		select	A,
    			max(date) as date
    		from	A
    		group by A) LastRecords
    		on A.A = LastRecords.A
    		and A.date = LastRecords.date
    ...but I can't promise that it is faster.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Another possibility is:
    Code:
    SELECT A.aid
              ,A.prj
              ,MAX(B.DATE)
    FROM A
       INNER JOIN B ON B.aid = A.aid
    GROUP BY A.aid, A.prj
    Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.

  5. #5
    Join Date
    Mar 2005
    Posts
    92
    Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a LEFT OUTER JOIN instead of INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2005
    Posts
    92
    Thanks for that

Posting Permissions

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