Home > DeveloperSection > Forums > Join a single row in one table to n random rows in another
Samuel Fernandes
Samuel Fernandes

Total Post:156

Points:1096
Posted on    May-06-2013 2:11 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 829  View(s)
Rate this:
Hi Everyone!


Is it possible to make a join in SQL server that joins each row from table A to n random rows in another? For example, say I have a Customer table, a Product table

and an Order table. I want to join each customer to 5 random products and insert these rows into the order table. (And each customer should be joined to 5 random rows

of his own, I don't want all customers joining to the same 5 rows).

Is this possible? I'm using SQL Server 2005 and it's fine if the solution is specific to that.

This is a weird requirement but I'm basically making a small data generator to generate some random data.

Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-06-2013 9:04 AM

Hi Samuel!

Have a look at something like this

DECLARE @Products TABLE(
        id Int,
        Prod VARCHAR(10)
)

DECLARE @Customer TABLE(
        id INT
)

INSERT INTO @Products SELECT 1, 'a'
INSERT INTO @Products SELECT 2, 'b'
INSERT INTO @Products SELECT 3, 'c'
INSERT INTO @Products SELECT 4, 'd'

INSERT INTO @Customer SELECT 1
INSERT INTO @Customer SELECT 2
--use a cross product select, BUT apply a random order number per customer,
--and only select the 'TOP N' items you require.
;WITH Vals AS (
        SELECT  c.id CustomerID,
                p.id ProductID,
                p.Prod,
                ROW_NUMBER() OVER( PARTITION BY c.ID ORDER BY NEWID()) RowNumber
        FROM    @Customer c,
                @Products p
)
SELECT  *
FROM    Vals
WHERE   RowNumber <= 2

Don't want to miss updates? Please click the below button!

Follow MindStick