JOIN codition in SQL Server

After applying join condition on two tables I want records which is maximum among records of left table

My query

SELECT a1.*, 
       t.*, 
       ( a1.trnratefrom - t.trnratefrom )AS minrate, 
       ( a1.trnrateto - t.trnrateto )    AS maxrate 
FROM   (SELECT a.srno, 
               trndate, 
               b.trnsrno, 
               Upper(Rtrim(Ltrim(b.trnstate)))   AS trnstate, 
               Upper(Rtrim(Ltrim(b.trnarea)))    AS trnarea,开发者_开发百科 
               Upper(Rtrim(Ltrim(b.trnquality))) AS trnquality, 
               Upper(Rtrim(Ltrim(b.trnlength)))  AS trnlength, 
               Upper(Rtrim(Ltrim(b.trnunit)))    AS trnunit, 
               b.trnratefrom, 
               b.trnrateto, 
               a.remark, 
               entdate 
        FROM   mstprodrates a 
               INNER JOIN trnprodrates b 
                 ON a.srno = b.srno)a1 
       INNER JOIN (SELECT c.srno, 
                          trndate, 
                          d.trnsrno, 
                          Upper(Rtrim(Ltrim(d.trnstate)))   AS trnstate, 
                          Upper(Rtrim(Ltrim(d.trnarea)))    AS trnarea, 
                          Upper(Rtrim(Ltrim(d.trnquality))) AS trnquality, 
                          Upper(Rtrim(Ltrim(d.trnlength)))  AS trnlength, 
                          Upper(Rtrim(Ltrim(d.trnunit)))    AS trnunit, 
                          d.trnratefrom, 
                          d.trnrateto, 
                          c.remark, 
                          entdate 
                   FROM   mstprodrates c 
                          INNER JOIN trnprodrates d 
                            ON c.srno = d.srno) AS t 
         ON a1.trnstate = t.trnstate 
            AND a1.trnquality = t.trnquality 
            AND a1.trnunit = t.trnunit 
            AND a1.trnlength = t.trnlength 
            AND a1.trnarea = t.trnarea 
            AND a1.remark = t.remark 
WHERE  t.srno = (SELECT MAX(srno) 
                 FROM   a1 
                 WHERE  srno < a1.srno) 

If you mean to say,

you want Records exist in Left table but not in right then use LEFT OUTER JOIN.