Last record of Join table

sql

Solution

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)

Problem

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

Original source