Select Random Number of Rows to Join Another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Random Number of Rows to Join Another Table

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-06-14 : 02:17:15

Hi,

I have 3 tables #tblUser, #tblColor and #tblUserColor like the following.

CREATE TABLE #tblUser (userID smallint PRIMARY KEY IDENTITY NOT NULL, username nvarchar (20) NOT NULL);

INSERT INTO #tblUser (username) VALUES ('Peter');
INSERT INTO #tblUser (username) VALUES ('Paul');
INSERT INTO #tblUser (username) VALUES ('Mary');

CREATE TABLE #tblColor (colorID smallint PRIMARY KEY IDENTITY NOT NULL, color nvarchar (20) NOT NULL);

INSERT INTO #tblColor (color) VALUES ('Red');
INSERT INTO #tblColor (color) VALUES ('Green');
INSERT INTO #tblColor (color) VALUES ('Blue');
INSERT INTO #tblColor (color) VALUES ('Yellow');

CREATE TABLE #tblUserColor (userColor smallint PRIMARY KEY IDENTITY NOT NULL, userID smallint, colorID smallint);

The third table #tblUserColor stores the favorite color(s) of each user. Each user can have up to 3 favorite colors.

How can I write an SQL statement that selects a random number (between 1 to 3) of colors for each user and insert into the third table? For example, Peter can have red & green, while blue & yellow & red for Paul and red for Mary.

Thanks,
ywb.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-14 : 02:37:19

Here is the SELECT part, I leave the INSERT part as an exercise for the reader

SELECT o.userID
      ,(SELECT TOP 1 colorID
        FROM #tblUserColor i
        WHERE i.userID = o.userID
        ORDER BY NEWID()) AS colorID
FROM #tblUser o

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 03:11:38

I would use the NEWID() function. There are lots of examples creating random number here at SQL Team forums.

Create 3 random number, join the #tblColor table and insert into #tblUserColor table for each user.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-14 : 06:11:46

Here's one way...

Using newid() I created a random integer between 1 and 100 for each combination of user and color, and then inserted based on that. I tried using a derived table but the newid() became not different for each row when I tried to do the insert (which was interesting) - so I ended up using a table variable instead (not very satisfying

--data
if object_id('#tblUser') is null drop table #tblUser
go
CREATE TABLE #tblUser (userID smallint PRIMARY KEY IDENTITY NOT NULL, username nvarchar (20) NOT NULL);

INSERT INTO #tblUser (username) VALUES ('Peter');
INSERT INTO #tblUser (username) VALUES ('Paul');
INSERT INTO #tblUser (username) VALUES ('Mary');

if object_id('#tblColor') is null drop table #tblColor
go
CREATE TABLE #tblColor (colorID smallint PRIMARY KEY IDENTITY NOT NULL, color nvarchar (20) NOT NULL);

INSERT INTO #tblColor (color) VALUES ('Red');
INSERT INTO #tblColor (color) VALUES ('Green');
INSERT INTO #tblColor (color) VALUES ('Blue');
INSERT INTO #tblColor (color) VALUES ('Yellow');

if object_id('#tblUserColor') is null drop table #tblUserColor
go
CREATE TABLE #tblUserColor (userColor smallint PRIMARY KEY IDENTITY NOT NULL, userID smallint, colorID smallint);

--calculation
declare @t table (userID smallint, colorID smallint, randomInt smallint)
insert @t
    select userID, colorId, abs(cast(cast(newid() as varbinary) as int)) % 100 + 1 as x
    from #tblUser, #tblColor
insert #tblUserColor select userID, colorID from @t where randomInt <= 50
select * from #tblUserColor

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-06-14 : 13:05:47

Hi everyone,

Thanks for the help!

I'm following RyanRandall's suggestion. However, is there any way that each user be limited to a maximum of 3 colors, regardless of how many colors there are in the color table?

Currently the script can assign all the 4 colors to a user, if so happens that the randomInt are all <= 50...

ywb.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-14 : 14:57:53

I guess you can exclude the 'highest' colour for each user like this...

--data
if object_id('#tblUser') is not null drop table #tblUser
go
CREATE TABLE #tblUser (userID smallint PRIMARY KEY IDENTITY NOT NULL, username nvarchar (20) NOT NULL);

INSERT INTO #tblUser (username) VALUES ('Peter');
INSERT INTO #tblUser (username) VALUES ('Paul');
INSERT INTO #tblUser (username) VALUES ('Mary');

if object_id('#tblColor') is not null drop table #tblColor
go
CREATE TABLE #tblColor (colorID smallint PRIMARY KEY IDENTITY NOT NULL, color nvarchar (20) NOT NULL);

INSERT INTO #tblColor (color) VALUES ('Red');
INSERT INTO #tblColor (color) VALUES ('Green');
INSERT INTO #tblColor (color) VALUES ('Blue');
INSERT INTO #tblColor (color) VALUES ('Yellow');

if object_id('#tblUserColor') is not null drop table #tblUserColor
go
CREATE TABLE #tblUserColor (userColor smallint PRIMARY KEY IDENTITY NOT NULL, userID smallint, colorID smallint);

--calculation
declare @t table (userID smallint, colorID smallint, randomInt smallint)
insert @t
    select userID, colorId, abs(cast(cast(newid() as varbinary) as int)) % 100 + 1 as x
    from #tblUser, #tblColor

insert #tblUserColor
select userID, colorID from @t a where randomInt <= 50
  and randomInt < (select max(randomInt) from @t where UserId = a.UserId)
select * from #tblUserColor

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-06-14 : 19:52:53

Hi RyanRandall,

But if my #tblColor has dozens of colors and I'd like to assign 3 or less to each user, this method won't work, will it?

ywb.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-15 : 02:27:00

It seems I miss read the requirements the first time round, what you ask for is double randomness to FILL the tblUserColor not to pick a random color from it.

This will only work in 2005 (as I recall)

INSERT #tblUserColor
SELECT userID, colorID
FROM #tblUser
CROSS JOIN (
   SELECT TOP (abs(cast(cast(newid() as varbinary) as int)) % 3 + 1) colorID
   FROM #tblColor C
   ORDER BY NEWID()) AS d2

I read Ryan's proposal and was puzzled that he used that wierd construction with NEWID() to get random numbers, he is usually a really smart guy and why would he have overlooked RAND().
Untill I tried out using RAND() ... damn what a stupid function, I learned a lot today.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-15 : 05:36:59

quote:
But if my #tblColor has dozens of colors and I'd like to assign 3 or less to each user, this method won't work, will it?

Nope. See my sig

Try this...

--data
if object_id('tempdb..#tblUser') is not null drop table #tblUser
go
CREATE TABLE dbo.#tblUser (userID smallint PRIMARY KEY IDENTITY NOT NULL, username nvarchar (20) NOT NULL);

INSERT INTO #tblUser (username) VALUES ('Peter');
INSERT INTO #tblUser (username) VALUES ('Paul');
INSERT INTO #tblUser (username) VALUES ('Mary');

if object_id('tempdb..#tblColor') is not null drop table #tblColor
go
CREATE TABLE #tblColor (colorID smallint PRIMARY KEY IDENTITY NOT NULL, color nvarchar (20) NOT NULL);

INSERT INTO #tblColor (color) VALUES ('Red');
INSERT INTO #tblColor (color) VALUES ('Green');
INSERT INTO #tblColor (color) VALUES ('Blue');
INSERT INTO #tblColor (color) VALUES ('Yellow');
INSERT INTO #tblColor (color) VALUES ('Pink');
INSERT INTO #tblColor (color) VALUES ('Purple');
INSERT INTO #tblColor (color) VALUES ('Orange');
INSERT INTO #tblColor (color) VALUES ('Indigo');
INSERT INTO #tblColor (color) VALUES ('Violet');

if object_id('tempdb..#tblUserColor') is not null drop table #tblUserColor
go
CREATE TABLE #tblUserColor (userColor smallint PRIMARY KEY IDENTITY NOT NULL, userID smallint, colorID smallint);

--calculation
declare @t table (userID smallint, colorID smallint, randomInt int)
insert @t
    select userID, colorId, abs(cast(cast(newid() as varbinary) as int)) as x
    from #tblUser, #tblColor

insert #tblUserColor
select userID, colorId from @t a
where randomInt % 100 < 50
  and (select count(*) from @t where userID = a.userID and randomInt < a.randomInt) < 3

select * from #tblUserColor

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-15 : 05:42:11

quote:
Originally posted by PSamsig

It seems I miss read the requirements the first time round, what you ask for is double randomness to FILL the tblUserColor not to pick a random color from it.

This will only work in 2005 (as I recall)
INSERT #tblUserColor
SELECT userID, colorID
FROM #tblUser
CROSS JOIN (
SELECT TOP (abs(cast(cast(newid() as varbinary) as int)) % 3 + 1) colorID
FROM #tblColor C
ORDER BY NEWID()) AS d2

I read Ryan's proposal and was puzzled that he used that wierd construction with NEWID() to get random numbers, he is usually a really smart guy and why would he have overlooked RAND().
Untill I tried out using RAND() ... damn what a stupid function, I learned a lot today.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Nice work for 2005, PSamsig. Glad they've improved the TOP operator

And yes - damn stupid RAND() function!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 06:11:50

quote:
Originally posted by RyanRandall

And yes - damn stupid RAND() function!

This is all from BOL that can be connected to Ryan's case.

Remarks
Repetitive invocations of RAND() in a single query will produce the same value.

I guess derived table falls under this category, Repetitive invocations.

Peter Larsson
Helsingborg, Sweden

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-06-15 : 13:07:38

Thanks, guys!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources