Joining two table based on few conditions which results in some TOP data from two tables

Question

I am trying to Create a SQL View by joining two SQL tables and return the latest value from second table and all the rows from first table similar to left join along with the TOP 1 record from table2 where there is no match in table 1 grouped by product,grade,term

My problem can be clearly explained with the below example.

Table1

Id  Product Grade Term Bid Offer 
100 ABC       A   Q1   10  20
101 ABC       A   Q1   5   25
102 XYZ       A   Q2   25  30
103 XYZ       B   Q2   20  30

Table2

Id Product Grade Term TradeValue
1  ABC     A     Q1     100
2  ABC     A     Q1     95    
3  XYZ     B     Q2     100
4  PQR     C     Q2     100
5  PQR     C     Q2     200

In the above data I want to join Table1 and Table2 when ever the columns Product,Grade and Term from both the tables are equal and return all the rows from Table1 while joining the latest Value of the column TradeValue from Table2 to the first record of the match and making TradeValue as NULL for other rows of the resultant View and the resultant View should have the Id of Table2 as LTID and it also should return TOP 1 row from Table 2 where Product,Grade and Term are not equal grouping by Product,Grade and Term

So the resultant SQL View should be

RESULT

   Id  Product Grade Term Bid Offer TradeValue LTID
   100  ABC       A   Q1   10   20    100         2
   101  ABC       A   Q1   5    25    NULL       NULL
   102  XYZ       A   Q2   25   30    NULL       NULL
   103  XYZ       B   Q2   20   30    100         3
   104  PQR       C   Q2   NULL NULL  200         5 

I tried using the following query

http://sqlfiddle.com/#!3/e8884/7

As we can see latest value of TradeValue being assigned to all matching rows in Table1 and also I was not able to return the TOP 1 row from Table2 where a match was not found

May I know a better way to deal with this?

Answer 1

Try this one :

SELECT ROW_NUMBER() OVER(ORDER BY d.Product) as ID,d.*,t1.Bid,t1.Offer,t2.LastTradeValue,t2.ID AS LTID 
FROM
(
  SELECT Product,Grade,Term
  FROM Table1
  UNION 
  SELECT Product,Grade,Term
  FROM Table2 
) d
LEFT JOIN Table1 t1 ON d.Product=t1.Product AND d.Grade=t1.Grade AND d.Term=t1.Term
OUTER APPLY 
(
  SELECT TOP 1 * FROM Table2 t2 WHERE d.Product=t2.Product AND d.Grade=t2.Grade AND d.Term=t2.Term
  ORDER BY t2.ID DESC
) t2

Answer 2

Since you are using Sql Server 2012 let me suggest the following:

DECLARE @t1 TABLE
    (
      Id INT ,
      Product CHAR(3) ,
      Grade CHAR(1) ,
      Term CHAR(2) ,
      Bid INT ,
      Offer INT
    )
DECLARE @t2 TABLE
    (
      Id INT ,
      Product CHAR(3) ,
      Grade CHAR(1) ,
      Term CHAR(2) ,
      TradeValue INT
    )

INSERT  INTO @t1
VALUES  ( 100, 'ABC', 'A', 'Q1', 10, 20 ),
        ( 101, 'ABC', 'A', 'Q1', 5, 25 ),
        ( 102, 'XYZ', 'A', 'Q2', 25, 30 ),
        ( 103, 'XYZ', 'B', 'Q2', 20, 30 )

INSERT  INTO @t2
VALUES  ( 1, 'ABC', 'A', 'Q1', 100 ),
        ( 2, 'ABC', 'A', 'Q1', 95 ),
        ( 3, 'XYZ', 'B', 'Q2', 100 ),
        ( 4, 'PQR', 'C', 'Q2', 100 ),
        ( 5, 'PQR', 'C', 'Q2', 200 ),
        ( 6, 'TTT', 'C', 'Q2', 200 ),
        ( 7, 'TTT', 'C', 'Q2', 201 ),
        ( 8, 'JJJ', 'C', 'Q2', 500 );

WITH    cte
          AS ( SELECT   t1.Id AS t1Id ,
                        t1.Product AS t1Product ,
                        t1.Grade AS t1Grade ,
                        t1.Term AS t1Term ,
                        t1.Bid AS t1Bid ,
                        t1.Offer AS t1Offer ,
                        IIF(t1.ID IS NULL, t1.Id, t2.Id) AS ordert2Id ,
                        t2.Id AS t2Id ,
                        t2.Product AS t2Product ,
                        t2.Grade AS t2Grade ,
                        t2.Term AS t2Term ,
                        t2.TradeValue AS t2TradeValue
               FROM     @t2 t2
                        FULL JOIN @t1 t1 ON t2.Product = t1.Product
                                            AND t2.Grade = t1.Grade
                                            AND t2.Term = t1.Term
             ),
        cte2
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY t2Product, t2Grade, t2Term, t1Id ORDER BY ordert2Id ) AS rn1 ,
                        RANK() OVER ( PARTITION BY t2Product, t2Grade, t2Term ORDER BY t1Id ) AS rn2 ,
                        LAST_VALUE(t2TradeValue) OVER ( PARTITION BY t2Product, t2Grade, t2Term ORDER BY ordert2Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TradeValue ,
                        LAST_VALUE(t2Id) OVER ( PARTITION BY t2Product, t2Grade, t2Term ORDER BY t2Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS TradeId
               FROM     cte
             )
    SELECT  t1Id AS ID ,
            ISNULL(t1Product, t2Product) AS Product ,
            ISNULL(t1Grade, t2Grade) AS Product ,
            ISNULL(t1Term, t2Term) AS Product ,
            t1Bid AS Bid ,
            t1Offer AS Offer,
            IIF(rn2 = 1, TradeValue, NULL) AS TradeValue,
            IIF(rn2 = 1, TradeId, NULL) AS LTID

    FROM    cte2
    WHERE   rn1 = 1

Output:

ID   Product    Product Product Bid  Offer  TradeValue  LTID
NULL PQR        C       Q2      NULL NULL   200         5
100  ABC        A       Q1      10   20     95          2
101  ABC        A       Q1      5    25     NULL        NULL
102  XYZ        A       Q2      25   30     NULL        NULL
103  XYZ        B       Q2      20   30     100         3