Last record of Join table

Question

I am lookign for the correct SQL code to join 2 tables and show only the last record of the details table.

I have a DB with 2 tables,

Deals 
   DealID
   Dealname
   DealDetails

DealComments
   dcID
   DealID
   CommentTime
   CommentPerson
   Comment

There are multiple comments for each Deal, but i would like to create a VIEW that displays all of the Deals and only the last Comment from each Deal (determined by the CommentTime) field

Answer 1

select a.dealid
, a.dealname
, a.dealdetails
, b.dcid
, b.commenttime
, b.commentperson
, b.comment
from deals a, dealcomments b
where b.dealid = a.dealid
  and b.commenttime = (select max(x.commenttime)
                       from dealcomments x
                       where x.dealid = b.dealid)

EDIT: I didn't read the initial question close enough and didn't notice that all DEALS rows were needed in the view. Below is my revised answer:

select a.dealid
, a.dealname
, a.dealdetails
, b.dcid
, b.commenttime
, b.commentperson
, b.comment
from deals a left outer join (select x.dcid
, x.dealid
, x.commenttime
, x.commentperson
, x.comment
from dealcomments x
where x.commenttime = (select max(x1.commenttime)
                       from dealcomments x1
                       where x1.dealid = x.dealid)) b
on (a.dealid = b.dealid)

Answer 2

Try this..

SELECT D.*,DC1.Comment 
FROM Deals AS D
   INNER JOIN DealComments AS DC1
        ON D.DealId = DC1.DealID
    INNER JOIN 
    (
        SELECT 
            DealID,
            MAX(CommentTime) AS CommentTime
        FROM DealComments AS DC2
        GROUP BY DealID
    ) AS DC2
        ON DC2.DealId = DC.DealId
            AND DC2.CommentTime = DC1.CommentTime

Answer 3

Not very elegant, but works in Oracle :

select dealid,
       dealname,
       dealdetails,
       comment,
from
(
  select a.dealid,
         a.dealname,
         a.dealdetails,
         b.commenttime,
         b.comment,
         max(commenttime) over (partition by a.dealid) as maxCommentTime
  from deals a inner join dealcomments b on b.dealid = a.dealid
)
where comment = maxCommentTime

Answer 4

Try this:

CREATE VIEW DealsWithLastComment
AS
   SELECT
       D.*, DC.*
   FROM
       Deals D INNER JOIN DealComments DC
       ON D.DealID = DC.DealID
       GROUP BY D.DealID, DC.CommentTime
       HAVING DC.CommentTime = MAX(DC.CommentTime)

Answer 5

select
  d.DealID, dc1.dcID, dc1.Comment, dc1.CommentPerson, dc1.CommentTime
from
  Deals d
inner join
  DealComments dc1 on dc1.DealID = d.DealID
where
  dc1.CommentTime = (select max(dc2.CommentTime) from DealsComments dc2 where dc2.DealId = dc1.DealId)

Answer 6

Obligatory no-subquery-nowhere-answer:

select d.*
       , dc.*
from   Deals d
       left outer join DealComments dc 
       on d.DealID = dc.DealID
       left outer join DealComments dc1 
       on d.DealID = dc1.DealID 
   and 
       dc1.CommentTime > dc.CommentTime
where  dc1.CommentTime is null

Show me everything in Deals and DealComments when there exists no CommentTime greater than any given comment time for a particular DealID.

Edit: as Alex Kuznetsov astutely points out in a comment: the OP requested that all deals be displayed -- whether a deal has a comment or not. So I have changed the first JOIN from INNER to LEFT OUTER.