sql random field infomation from unrealted table

I have a table of image records in an MS SQL table TBLimages with a field imageFilename with 1,000 records.

I have another separate table of listing possible Fonts with no relationship to the image file with 100 records with a key field of FontId

I want to create an SQL statement that will provide only all the records from TBLimages but with an extra column for a random valid value of FontId from the Font table

But I don't want extra records created and cannot use a group by as too many fields in TBLimages .

Help would be appreciated.
stephenwildeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
You can use ORDER BY NEWID() to generate random data. Here the TOP 1 clause is pulling a random font because the font table is being ordered by a randomly-generated GUID:
SELECT Field1,
Field2,
Field3,
Field4,
(SELECT TOP 1 FontID from TBLFonts ORDER BY NEWID()) AS TestFontID
FROM TBLimages

Open in new window

0
stephenwildeAuthor Commented:
Thank you,  if you run the SQL several times each time it comes back with a different random FontId

But the problem is the same FontId is against all the records

Looking for a different random FontId for each record in the SQL result.
0
Olaf DoschkeSoftware DeveloperCommented:
I wonder, why you think a group by depends on the number of fields. Grouping is not wanted here at all, is it?

If you don't care whether each font is used at least once you can expand on the idea of Russel and create a random font id by a random number you generate by ABS(CHECKSUM(NewId())) % 100+1 for numbers between 1 and 100.

Random numbers and why you can't simply use RAND() explained in detail: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-set-based-random-numbers/

In your case if Font IDs are from 1 to 100 you don't even need to join the font table, simply do

SELECT *, ABS(CHECKSUM(NewId()))%100 + 1 as FontID FROM TBLimages

Open in new window


More general you could determine Max fontid first and put it into the query instead of 100:

DECLARE @MaxFontID AS int;

SELECT @MaxFontID = MAX(FontID) FROM TBLfonts;

SELECT *, ABS(CHECKSUM(NewId()))%@MaxFontID + 1 as FontID FROM TBLimages;

Open in new window


Bye, Olaf.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stephenwildeAuthor Commented:
Thanks for replies.

I didn't want to make the problem to specific originally, so didn't go in this detail.

But the font table doesn't exactly for 100 records and it has a flag bool field of isLive, so not all font id's are suitable,
so a random number between 1 and max number numbers will not work here, but thanks for the suggestion.
0
Olaf DoschkeSoftware DeveloperCommented:
Then a bit more complex

DECLARE @Reccount AS int;

SELECT @Reccount = Count(*) FROM TBLfonts Where isLive=1;

;With RNDFonts AS
(SELECT ROW_NUMBER() OVER(ORDER BY FontID) as RowNum, FontID FROM TLBfonts Where isLive=1)

SELECT TBLimages.*, RNDFonts.FontID FROM TBLimages
INNER JOIN RNDFonts On RowNum = ABS(CHECKSUM(NewId()))%@Reccount + 1

Open in new window


Bye, Olaf.
0
stephenwildeAuthor Commented:
Thanks very inventive using the row number.

But when run on the data although the Select;
(SELECT ROW_NUMBER() OVER(ORDER BY FontID) as RowNum, FontID FROM TLBfonts Where isLive=1)
produces records
when added with inner join on the last Select : NO records are produced at all.
0
Olaf DoschkeSoftware DeveloperCommented:
I see - if you try with just a few records you get some results, but seldom all images. The problem is the way SQL Server evaluates the join condition, it doesn't create one random value per image and finds the RowNum in RNDFonts, it scans RNDFonts and you have to be very lucky the random value matches RowNum.

So we have to do it with 2 CTEs:

Declare @Reccount AS int;

SELECT @Reccount = Count(*) FROM TBLfonts Where isLive=1

;With RNDfonts AS
(SELECT ROW_NUMBER() OVER(ORDER BY FontID) as RowNum, FontID FROM TBLfonts WHERE isLive=1)
, RNDimages AS
(SELECT ABS(CHECKSUM(NewId()))%@Reccount + 1 as RandomRowNum, ImageID FROM TBLimages)

SELECT ImageID, FontID FROM RNDimages LEFT JOIN RNDFonts On  RandomRowNum = RowNum

Open in new window

Edit: For some reason it has to be a LEFT join in the final query, not an INNER join. But changing just that in the old solution does not solve it, the whole thing only works with the RNDimages CTE.

Bye, Olaf.
0
stephenwildeAuthor Commented:
Thank you the solution you suggested did indeed product random valid Font ID in TBLimages.

Your solution is something well beyond my ability and it does work.

However all the FontID's are all in growing sequence  with higher numbers in TBLimages.

so random numbers generated;
record 1 fontid = 4
record 2 fontid =10
record 3 fontid=12
etc.

Prefer a solution that generates a truly random valid FontID for each record.
0
Olaf DoschkeSoftware DeveloperCommented:
Really?

I get random results in fontids with this sqlfiddle.

It creates a random valid fontid (of a font that isLive = 1) for every imageid, not rising sequence fontids.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stephenwildeAuthor Commented:
Thanks for persisting with the solution,
 I had an extra order by on my SQL code once this was adjusted for it was a random FontID.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.