Last entry in the Join table

Question

I am looking for the correct SQL code to join 2 tables and show only the last detail table entry.

I have a DB with two tables,

Deals 
   DealID
   Dealname
   DealDetails

DealComments
   dcID
   DealID
   CommentTime
   CommentPerson
   Comment

There are several comments for each Transaction, but I would like to create a VIEW that displays all Transactions and only the last Comment from each Deal field (defined in CommentTime)

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 did not read the initial question close enough and did not notice that all DEALS lines are required 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 it.

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

, 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

:

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

Required no-subquery answer anywhere:

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 Dealsand DealCommentswhen there is CommentTimemore than any given comment time for a particular one DealID.

Change: as Alex Kuznetsov briefly points out a comment: OP requested to show all deals - does the deal have a comment or not. Therefore, I changed the first JOINfrom INNERto LEFT OUTER.

Answer 7

How about this request.

select * from Deals 
left join DealComments on Deals.DealID = DealComments.DealID and DealComments.CommentTime = (SELECT CommentTime  FROM DealComments WHERE DealComments.DealID = Deals.DealID ORDER BY CommentTime DESC limit 1)