How to Write Left Outer join for this SQL query?

Question

I had a table like

ParsereplyId   Message   Callid   status    EMailid       subject 

------------------------------------------------------------------------
      1        hi         2222   Replied   g.m.p@m.com    Log a cll
      2        hello      2222   Replied   g.m.p@m.com    Re: 
      3        hi2        2222   New       g.m.p@m.com    Re:log a cll
      4        hello2     2223   Read      g.p@m.com      Log a cldf
      5        how r u    2223   New       g.p@m.com      Re:Log a 

From the above table i want to get the following output:

ParsereplyId Message  Callid  status    EMailid       subject 

-------------------------------------------------------------------
      3      hi2       2222   New      g.m.p@m.com    Re:log a cll
      5      how r u   2223   New      g.p@m.com      Re:Log a 

I have tried the following query. but i would like to do it in Left outer Join

SELECT  A.[ParsedReplyId]
                , A.[EMailId]
                , A.[Message] 
                , A.[CallId]   
                , [UM_User].[UserName]                  
FROM IM_IncidentReplyMail AS A 
        INNER JOIN
                (SELECT  MAX(ParsedReplyId) AS parseid 
                 FROM [IM_IncidentReplyMail]  
                 GROUP BY [CallId]) AS B 
        INNER JOIN   
         [UM_User] ON [UM_User].[EmailId] = A.[EmailId] 
        WHERE 
            B.parseid = A.[ParsedReplyId] 

how to implement above sql query to the inner join??

Edit: I Corrected my mistake above

UM_USer table contains username, emailid, based on email id from two table i can get the username of that mail.

Edit 2: I can use Left outer join instead of using inner join like

 SELECT  A.[ParsedReplyId]
                    , A.[EMailId]
                    , A.[Message] 
                    , A.[CallId]   
                    , [UM_User].[UserName]                  
            FROM IM_IncidentReplyMail AS A 
            LEFT OUTER JOIN
                    (SELECT  MAX(ParsedReplyId) AS parseid 
                     FROM [IM_IncidentReplyMail]  
                     GROUP BY [CallId]) AS B 
            ON 1=1
            LEFT OUTER  JOIN   
             [UM_User] ON [UM_User].[EmailId] = A.[EmailId] 
            WHERE 
                B.parseid = A.[ParsedReplyId]

Here I used 1=1 condition which is not the right way to use in queries. it makes my code ugly. so i am asking you help to resolve this.

Answer 1

I have modified the query according to your required output

    DECLARE @Table1 TABLE 
        (Id INT, Message VARCHAR(7), Call INT, status VARCHAR(7), EMail VARCHAR(11), subject VARCHAR(12))
    ;

    INSERT INTO @Table1
        (Id, Message, Call, status, EMail, subject)
    VALUES
        (1, 'hi', 2222, 'Replied', 'g.m.p@m.com', 'Log a cll'),
        (2, 'hello', 2222, 'Replied', 'g.m.p@m.com', 'Re:'),
        (3, 'hi2', 2222, 'New', 'g.m.p@m.com', 'Re:log a cll'),
        (4, 'hello2', 2223, 'Read', 'g.p@m.com', 'Log a cldf'),
        (5, 'how r u', 2223, 'New', 'g.p@m.com', 'Re:Log a')
    ;

        SELECT T.Id,
        TT.Message,
        TT.Call,
        TT.status,
        TT.EMail,
        TT.subject 
        FROM @Table1 tt 
        LEFT OUTER JOIN 
        (
        SELECT Id,
        MAX(subject) OVER(PARTITION BY call ORDER BY call) subject,
        MAX(Message) OVER(PARTITION BY call ORDER BY call) Message  FROM @Table1)T
        ON T.Id = TT.Id AND T.Message = TT.Message AND T.subject = TT.subject
WHERE T.id IS NOT NULL