Limit the join to only the first image ID, can't think how best to do this but MIN(I.ImageID) somewhere might help.
Ade -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 28 May 2004 14:39 To: [EMAIL PROTECTED] Subject: [ cf-dev ] SQL question Got two tables Properties and Images, in a one-to-many relationship: one property can have many images. I want to pick one property at random, and display the first image belonging to that property, but can't figure out how to do this. It's easy enough to just display any image at random: <cfquery name="getImages" datasource="dsn"> SELECT Imagename FROM Images I, Properties P WHERE I.PropertiesId = P.ID </cfquery> <cfset random = RandRange(1,getImages.RecordCount)> <cfoutput><img src="images/#getImags.Imagename[random]#"></cfoutput> However, how to limit it to just the first image for any random property, eg: Property A Image A1, Image A2, Image A3 Property B Image B1 Property C Image C1, Image C2, Image C3, Image C4 I want to display either A1, B1 or C1 at random.... any ideas? So far I'm trying combinations of grouping the output on the propertyID, and picking a random startrow, but not managing to get it doing what I want. -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED] -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
