---
title: "Join a single row in one table to n random rows in another"  
description: "Join a single row in one table to n random rows in another"  
author: "Samuel Fernandes"  
published: 2013-05-06  
updated: 2013-05-06  
canonical: https://www.mindstick.com/forum/825/join-a-single-row-in-one-table-to-n-random-rows-in-another  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# Join a single row in one table to n random rows in another

Hi Everyone!\
\
\
Is it possible to make a [join in SQL](https://www.mindstick.com/forum/33686/what-is-a-self-join-in-sql-server) server that joins each row from table A to n [random rows](https://www.mindstick.com/forum/828/how-do-i-select-random-rows-from-a-database-with-a-twist) in another? For example, say I have a [Customer](https://www.mindstick.com/articles/325839/how-to-improve-your-business-level-of-customer-service) table, a [Product](https://www.mindstick.com/articles/75385/full-product-keys) table \
\
and an Order table. I want to join each customer to 5 random [products](https://www.mindstick.com/news/2358/apple-will-use-us-made-semiconductors-in-its-products-to-lessen-its-dependency-on-asia) 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](https://www.mindstick.com/articles/13008/how-does-a-customer-portal-enhance-the-experience-of-customers) joining to the same 5 rows).\
\
Is this possible? I'm using [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) 2005 and it's fine if the solution is specific to that.\
\
This is a weird [requirement](https://yourviews.mindstick.com/view/85169/becoming-an-influencer-requirement-of-skills-and-knowledge) but I'm basically making a small data [generator](https://www.mindstick.com/articles/249204/generator-info-where-can-you-find-the-best-generator-for-power-outages) to generate some random data.\
\
Thanks in [advance](https://www.mindstick.com/blog/33258/jee-mains-and-jee-advance-exams)!

## Replies

### Reply by AVADHESH PATEL

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 1INSERT 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 ValsWHERE RowNumber <= 2


---

Original Source: https://www.mindstick.com/forum/825/join-a-single-row-in-one-table-to-n-random-rows-in-another

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.