CODEWITHSUNDEEP
sql-serverjoinsql-server-2012
DoIt
DoIt

Reputation: 3448

Joining two SQL tables based on the equality of few columns

I am trying to Create a SQL View by joining two SQL tables and return only the lowest value from second table and all the rows from first table similar to left join.

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

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 lowest 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

So the resultant SQL View should be

RESULT

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

I tried using the following query

CREATE VIEW [dbo].[ViewCC]
AS
SELECT
a.Id,a.Product,a.Grade,a.Term,a.Bid,a.Offer,
b.TradeValue
FROM Table1 AS a 
left  JOIN (SELECT Product,Grade,Term,MIN(TradeValue) TradeValue from  Table2 Group by Product,Grade,Term,) AS b
 ON  b.Product=a.Product
and b.Grade=a.Grade
and b.Term=a.Term 
GO

The above Query returned the following data which is apt to the query I wrote but that is not what I was trying to get

 Id  Product Grade Term Bid Offer TradeValue 
 100   ABC      A   Q1   10   20      95       
 101   ABC      A   Q1   5    25      95 --This should be null     
 102   XYZ      A   Q2   25   30     NULL     
 103   XYZ      B   Q2   20   30     100

As we can see minimum value of TradeValue being assigned to all matching rows in Table1 and also I was not able to return Id As LTID from Table2 as I have issues with group by clause as I cannot group it by b.Id as it returns too many rows.

May I know a better way to deal with this?

Upvotes: 0

Views: 1085

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You need a row number attached to each record from Table1, so that the requirement of only joining the first record from each group of Table1 can be fulfilled:

CREATE VIEW [dbo].[ViewCC]
AS
SELECT a.Id, a.Product, a.Grade, a.Term, a.Bid, a.Offer, 
       b.TradeValue, b.Id AS LTID
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Product, Grade, Term ORDER BY Id) AS rn
  FROM Table1
) a
OUTER APPLY (
  SELECT TOP 1 CASE WHEN rn = 1 THEN TradeValue
                    ELSE NULL
               END AS TradeValue, Id 
  FROM  Table2
  WHERE Product=a.Product AND Grade=a.Grade AND Term=a.Term
  ORDER BY TradeValue) b  
GO

OUTER APPLY returns a table expression containing either the matching record from Table2 with the lowest TradeValue, or NULL if no matching record exists.

Upvotes: 1

Related Questions