Join two tables with conditions depending on multiples columns

Question

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:

Table A and Table B have same number of rows, and UserId in both table are same set of numbers.
For any UserId, PayDate is always later than OrderDate
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?

Your question (and your structures) are not very clear. I think you might be able to utilize ROW_NUMBER here but really the tables need some help if you truly just have payments with no relevance to what is being paid. — Sean Lange, Jan 05 '15 at 16:49

score 2 · Accepted Answer · answered Jan 05 '15 at 16:52

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.

Thanks for the answer. Just out of curiosity, why there is a semicolon at the beginning? — user3684014, Jan 05 '15 at 19:10
@user3684014 semi-colons are going to become more and more prevalent in SQL Server. They are statement terminators i.e. "here's the end of this query". You put them at the beginning of CTE's as they are required (mostly when they are preceded by other code, but it is good practice to do them throughout), despite being optional for other, more standard queries. The semi-colon will eventually become a requirement, not just an option. — gvee, Jan 06 '15 at 09:32
@user3684014 this might be a bit clearer than my ramblings: http://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server . The second answer is greatly important, don't miss it! — gvee, Jan 06 '15 at 09:49
And finally, the manual states "Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version." (source: http://msdn.microsoft.com/en-us/library/ms177563(v=sql.120).aspx) — gvee, Jan 06 '15 at 09:51

score 0 · Answer 2 · answered Jan 05 '15 at 16:53

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

Join two tables with conditions depending on multiples columns

2 Answers2