CODEWITHSUNDEEP
sql-server
user3684014
user3684014

Reputation: 1175

Join two tables with conditions depending on multiples columns

In SQL Server 2008, I want to join two table on key that might have duplicate, but the match is unique with the information from other columns.

For a simplified purchase record example,

Table A:
UserId  PayDate Amount
1       2015    100
1       2010    200
2       2014    150

Table B:
UserId  OrderDate Count
1       2009      4
1       2014      2
2       2013      5

Desired Result:
UserId  OrderDate PayDate Amount Count
1       2009      2010    200    4
1       2014      2015    100    2
2       2013      2014    150    5

It's guaranteed that:

  1. Table A and Table B have same number of rows, and UserId in both table are same set of numbers.

  2. For any UserId, PayDate is always later than OrderDate

  3. Rows with same UserId are matched by sorted sequence of Date. For example, Row 1 in Table A should match Row 2 in Table B

My idea is that on both tables, first sort by Date, then add another Id column, then join on this Id column. But I not authorized to write anything into the database. How can I do this task?

Upvotes: 0

Views: 51

Answers (2)

Steve Ford
Steve Ford

Reputation: 7753

Try:

;WITH t1
AS
(
    SELECT UserId, PayDate, Amount, 
           ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY PayDate) AS RN
    FROM TableA
),
t2
AS
(
    SELECT UserId,  OrderDate, [Count],
           ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY OrderDate) AS RN
    FROM TableB
)
SELECT t1.UserId,  t2.OrderDate, t1.PayDate,  t1.Amount,  t2.[Count]
FROM t1
INNER JOIN t2
   ON t1.UserId = t2.UserId AND t1.RN = t2.RN

Upvotes: 0

gvee
gvee

Reputation: 17161

Row_Number() will be your friend here. It allows you to add a virtual sequencing to your resultset.

Run this and study the output:

SELECT UserID
     , OrderDate
     , "Count" As do_not_use_reserved_words_for_column_names
     , Row_Number() OVER (PARTITION BY UserID ORDER BY OrderDate) As sequence
FROM   table_b

The PARTITION BY determines when the counter should be "reset" i.e. it should restart after a change of UserID

The ORDER BY, well, you've guessed it - determines the order of the sequence!

Pull this all together:

; WITH payments AS (
  SELECT UserID
       , PayDate
       , Amount
       , Row_Number() OVER (PARTITION BY UserID ORDER BY PayDate) As sequence
  FROM   table_b
)
, orders AS (
  SELECT UserID
       , OrderDate
       , "Count" As do_not_use_reserved_words_for_column_names
       , Row_Number() OVER (PARTITION BY UserID ORDER BY OrderDate) As sequence
  FROM   table_b
)
SELECT orders.UserID
     , orders.OrderDate
     , orders.do_not_use_reserved_words_for_column_names
     , payments.PayDate
     , payments.Amount
FROM   orders
 LEFT
  JOIN payments
    ON payments.UserID   = orders.UserID
   AND payments.sequence = orders.sequence

P.S. I've opted for an outer join because I assumed that there's not always going to be a payment for every order.

Upvotes: 2

Related Questions