JOIN A SINGLE ROW IN ONE TABLE TO N RANDOM ROWS IN ANOTHER

Samuel Fernandes

Total Post:159

Points:1117
Posted by  Samuel Fernandes
 1088  View(s)
Ratings:
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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Join a single row in one table to n random rows in another

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!