Random Records Access Database RND() doesn't work - Cold Fusion

This is the ouput I have from my query:
Janome MC9700
Janome MC11000
Janome DC3018
Janome JEM720
Janome MC3500
Janome MC6500P
Janome MC4800QC
Janome 1600P
Janome 1600P

<CFQUERY NAME="rndpick" DATASOURCE="absi">
SELECT prodmat.*
FROM Catfit INNER JOIN ProdMat ON Catfit.prodid=ProdMat.prodid
WHERE catfit.modlid=1
</cfquery>

<!--- must handle case when the query contains less than 12 records --->
<cfset maxRecords = MIN(20, rndpick.recordCount)>
<cfset recordNumbers = structNew()>
<cfloop condition="structCount(recordNumbers) lt maxRecords">
<cfset num = randRange(1, rndpick.recordCount)>
<cfset recordNumbers[NUM] = num>
</cfloop>

<cfoutput>
<cfloop list="#structKeyList(recordNumbers)#" index="record">
#rndpick["brand"][record]# #rndpick["modl"][record]#<br>
</cfloop>
</cfoutput>

I have a somewhat normalized database and records with a one to many
relationship (I think). I have a product that fits into multiple categories but
for this purpose I just want it to show up once. This is very cool by the way!

Sorry last post is messed up at the end. I have a somewhat normalized database
with products that fit into many categories (one to many relationship?). For
this purpose I only want the product to show up once. here is an example of how
my db looks

Catfit
CID (autonumber) prodid (number value) modcat (number value)
1 23 8
2 23 9
3 29 1

Prodid in the cafit table is linked to the promat table and I need to grab
multiple columns from prodmat for output, so I believe that I cannot use the
GROUP BY function in the SQL query. I have tried it anyway and I get an error
about an aggregate function
4 32 5

I answered my own question after I posted. I just put the SELECT DISTINCT in the query. I was still thinking I was using ORDER BY RND(). Sorry and thank you for all of your help!!!!!!

SonOfNels wrote:
> I have a somewhat normalized database with products that fit into many
categories (one to many relationship?).

It sounds like a many-to-many relationship. So you have three tables:
Products, Categories and ProductCategory. With the ProductCategory table
holding the relationships (ie which products belong to which categories)?

ProductID, CategoryID
(product 1), (category A)
(product 1), (category B)
(product 2), (category A)
(product 2), (category C)
(product 2), (category F)


(ie which products belong to which categories).

> Prodid in the cafit table is linked to the promat table and I need to grab
multiple columns from
> prodmat for output, so I believe that I cannot use the GROUP BY function in
the SQL query.

Are you actually using the category information for the output? Because all I
saw in your sample was a product name.

[q]Originally posted by: SonOfNels
I answered my own question after I posted. I just put the SELECT DISTINCT in
the query. I was still thinking I was using ORDER BY RND(). Sorry and thank you
for all of your help!!!!!![/q]

I am glad it is working.

> SELECT prodmat.*

BTW, it is usually better to select only the columns you need instead of using
"*". Especially when using SELECT DISTINCT.