I have 2 tables: one with Customers and one with PromoCodes. I need to assign promocodes to Customers (1 code to 1 Customer), taking Promocodes in order and assigning to Customers, one by one.

And this is the outcome I would need:

Thank you so much!

CodePudding user response：

Provided your RDBMS supports window functions you can assign a surrogate key to each set using either derived tables or CTEs and use it to join:

select CustomerId, Firstname, PromoCode
from (
  select *, Row_Number() over(order by customerId) seq 
  from Customers
) c join (
  select *, Row_Number() over(order by promocode) seq 
  from PromoCodes
) p on c.seq = p.seq;

CodePudding user response：

You have to determine in which order you want the customers and promocodes. Then you can use ROW_NUMBER to assign an Id corresponding to the order. You can then combine the two tables using an inner join.

WITH OrderedCustomers AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerId) AS [Order#]
FROM Customers
), OrderedPromoCodes AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY Promocode) AS [Order#]
FROM Promocodes
)
SELECT * 
FROM OrderedCustomers
INNER JOIN OrderdPromoCodes b
ON a.[Order#] = b.[Order#]