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) FROMPRODUCTS 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=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Distinct on two columns?
select distinct pr_side,pr_type At 11:55 AM 3/5/2003 +, you wrote: More a question than an answer... as no time to test Would this work? SELECT DISTINCT (PR_SIDE || || PR_TYPE) FROMPRODUCTS 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=subscribeforumid=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
RE: Distinct on two columns?
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) FROMPRODUCTS 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=subscribeforumid=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
RE: Distinct on two columns?
Oh yeah that'd be right. The most obvious answer. And that's the one I haven't tried. DER! (smacks forehead with palm of hand) Thanks Jim, you're right. blush sheepish grin Cheers, Michael Kear Windsor, NSW, Australia AFP Webworks. Note to self: KEAR YOU BLOODY IDIOT - TRY THE OBVIOUS BEFORE YOU ASK AGAIN!! -Original Message- From: Jim Gurfein [mailto:[EMAIL PROTECTED] Sent: Wednesday, 5 March 2003 10:56 PM To: CF-Talk Subject: RE: Distinct on two columns? select distinct pr_side,pr_type At 11:55 AM 3/5/2003 +, you wrote: More a question than an answer... as no time to test Would this work? SELECT DISTINCT (PR_SIDE || || PR_TYPE) FROMPRODUCTS 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=subscribeforumid=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
