I hadn't thought about concatenating them. Good idea. But no it wont work because the second column is only relevant for types that have the word 'drum' in them. All other types, the second column has nothing to do with it.
Cheers, Michael Kear Windsor, NSW, Australia AFP Webworks. -----Original Message----- From: David Collie (itndac) [mailto:[EMAIL PROTECTED] Sent: Wednesday, 5 March 2003 10:55 PM To: CF-Talk Subject: RE: Distinct on two columns? More a question than an answer... as no time to test Would this work? SELECT DISTINCT (PR_SIDE || " " || PR_TYPE) FROM PRODUCTS Ie concantenate them and use distinct on that? -----Original Message----- From: Michael Kear [mailto:[EMAIL PROTECTED] Sent: 05 March 2003 11:05 To: CF-Talk Subject: Distinct on two columns? I have to dynamically generate a drop-down list, so that if a column in a data table contains a certain type, it is included in the drop down list. So far no problem. For example, the drop down list has to include all the different types in the "type" column. But where I'm stumped is that if the type includes "drum", I have to look in the loading column and see if 'front' or 'top' is in there. If there are any values with 'drum' in the type column and 'front' in the loading column, I have to include 'Front loading drum' in the drop down list. But if not, I have to omit that value from the drop down list. How can I do this without doing a whole succession of queries? One way to do it is <cfif query1.type contains 'drum'>another query on the table to find the distinct values of loading where type like '%drum%' If this query gives recordcount greater than zero, include the value in the drop down.<cfif> Is there a better way? This seems awfully clumsy and processor intensive. Cheers, Michael Kear Windsor, NSW, Australia AFP Webworks. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
