Re: apologies...query display balled up
Tim, Use the keyword DISTINCT. I am guessing that because record #128 passes both tests, it is getting two result rows. I am not certain, but I think it will do the trick. Take care, -- Original Message -- From: Tim Laureska [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 18 Oct 2002 11:02:36 -0400 Man.. I apologize for how the first message came across formatted weird.please let me try again: I have an event calendar access database with two tables (one called category with two fields: cat_no and descr.. the other table is called events with many fields including two called category and category2) . Events can have one or two categories and each event should be reflected on one row of the table with description, location etc. The event_no is unique for each event The Table displays as shown below, but for example event# 128 should only come up on one row with Fine Food Beverage; Arts, Theatre Concerts shown under Category Description what's wrong with my code below ?? Event no. Category Description LOCATION CATEGORY CATEGORY2 128Fine Food Beverage; St Michaels; 1 5 126Sports - Youth; St Michaels; 1 2 128Arts, Theatre Concerts; St Michaels; 1 5 126Arts, Theatre Concerts; St Michaels; 1 2 cfquery datasource=cal_endar_2 name=findcal SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no /cfquery table cfoutput query=findcal group=event_no tr td valign=top#event_no#/td tdcfoutput#descr#;/cfoutput/td td#town#/td td#category#/td td#category2#/td /tr /cfoutput /table Thanks in Advance Tim Laureska ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: apologies...query display balled up
I don't think this will do it as the distinct operator works on ALL of the columns combined in the select clause and in this case each is unique so you will still get the same data back. What you need is to run the same query as you have but with an order by like so... SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no ORDER BY event_no then continue using the group by in the cfoutput as you have. -Original Message- From: Robert Polickoski [mailto:rpolickoski;isrd.com] Sent: 18 October 2002 03:46 To: CF-Talk Subject: Re: apologies...query display balled up Tim, Use the keyword DISTINCT. I am guessing that because record #128 passes both tests, it is getting two result rows. I am not certain, but I think it will do the trick. Take care, -- Original Message -- From: Tim Laureska [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 18 Oct 2002 11:02:36 -0400 Man.. I apologize for how the first message came across formatted weird.please let me try again: I have an event calendar access database with two tables (one called category with two fields: cat_no and descr.. the other table is called events with many fields including two called category and category2) . Events can have one or two categories and each event should be reflected on one row of the table with description, location etc. The event_no is unique for each event The Table displays as shown below, but for example event# 128 should only come up on one row with Fine Food Beverage; Arts, Theatre Concerts shown under Category Description what's wrong with my code below ?? Event no. Category Description LOCATION CATEGORY CATEGORY2 128Fine Food Beverage; St Michaels; 1 5 126Sports - Youth; St Michaels; 1 2 128Arts, Theatre Concerts; St Michaels; 1 5 126Arts, Theatre Concerts; St Michaels; 1 2 cfquery datasource=cal_endar_2 name=findcal SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no /cfquery table cfoutput query=findcal group=event_no tr td valign=top#event_no#/td tdcfoutput#descr#;/cfoutput/td td#town#/td td#category#/td td#category2#/td /tr /cfoutput /table Thanks in Advance Tim Laureska ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: apologies...query display balled up
Andy .. using the ORDER BY event_no works, but can you help me understand why that eliminates the duplicates? -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Friday, October 18, 2002 11:56 AM To: CF-Talk Subject: RE: apologies...query display balled up I don't think this will do it as the distinct operator works on ALL of the columns combined in the select clause and in this case each is unique so you will still get the same data back. What you need is to run the same query as you have but with an order by like so... SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no ORDER BY event_no then continue using the group by in the cfoutput as you have. -Original Message- From: Robert Polickoski [mailto:rpolickoski;isrd.com] Sent: 18 October 2002 03:46 To: CF-Talk Subject: Re: apologies...query display balled up Tim, Use the keyword DISTINCT. I am guessing that because record #128 passes both tests, it is getting two result rows. I am not certain, but I think it will do the trick. Take care, -- Original Message -- From: Tim Laureska [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 18 Oct 2002 11:02:36 -0400 Man.. I apologize for how the first message came across formatted weird.please let me try again: I have an event calendar access database with two tables (one called category with two fields: cat_no and descr.. the other table is called events with many fields including two called category and category2) . Events can have one or two categories and each event should be reflected on one row of the table with description, location etc. The event_no is unique for each event The Table displays as shown below, but for example event# 128 should only come up on one row with Fine Food Beverage; Arts, Theatre Concerts shown under Category Description what's wrong with my code below ?? Event no. Category Description LOCATION CATEGORY CATEGORY2 128Fine Food Beverage; St Michaels; 1 5 126Sports - Youth; St Michaels; 1 2 128Arts, Theatre Concerts; St Michaels; 1 5 126Arts, Theatre Concerts; St Michaels; 1 2 cfquery datasource=cal_endar_2 name=findcal SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no /cfquery table cfoutput query=findcal group=event_no tr td valign=top#event_no#/td tdcfoutput#descr#;/cfoutput/td td#town#/td td#category#/td td#category2#/td /tr /cfoutput /table Thanks in Advance Tim Laureska ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: apologies...query display balled up
I'd guess it's because GROUP BY is in itself a distinct clause. You'd understand it better if there was another field being counted. Ade -Original Message- From: Tim Laureska [mailto:hometeam;goeaston.net] Sent: 18 October 2002 17:19 To: CF-Talk Subject: RE: apologies...query display balled up Andy .. using the ORDER BY event_no works, but can you help me understand why that eliminates the duplicates? -Original Message- From: Andy Ewings [mailto:andy.e;thoughtbubble.net] Sent: Friday, October 18, 2002 11:56 AM To: CF-Talk Subject: RE: apologies...query display balled up I don't think this will do it as the distinct operator works on ALL of the columns combined in the select clause and in this case each is unique so you will still get the same data back. What you need is to run the same query as you have but with an order by like so... SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no ORDER BY event_no then continue using the group by in the cfoutput as you have. -Original Message- From: Robert Polickoski [mailto:rpolickoski;isrd.com] Sent: 18 October 2002 03:46 To: CF-Talk Subject: Re: apologies...query display balled up Tim, Use the keyword DISTINCT. I am guessing that because record #128 passes both tests, it is getting two result rows. I am not certain, but I think it will do the trick. Take care, -- Original Message -- From: Tim Laureska [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 18 Oct 2002 11:02:36 -0400 Man.. I apologize for how the first message came across formatted weird.please let me try again: I have an event calendar access database with two tables (one called category with two fields: cat_no and descr.. the other table is called events with many fields including two called category and category2) . Events can have one or two categories and each event should be reflected on one row of the table with description, location etc. The event_no is unique for each event The Table displays as shown below, but for example event# 128 should only come up on one row with Fine Food Beverage; Arts, Theatre Concerts shown under Category Description what's wrong with my code below ?? Event no. Category Description LOCATION CATEGORY CATEGORY2 128Fine Food Beverage; St Michaels; 1 5 126Sports - Youth; St Michaels; 1 2 128Arts, Theatre Concerts; St Michaels; 1 5 126Arts, Theatre Concerts; St Michaels; 1 2 cfquery datasource=cal_endar_2 name=findcal SELECT * FROM events e, category c where e.category=c.cat_no or e.category2=c.cat_no /cfquery table cfoutput query=findcal group=event_no tr td valign=top#event_no#/td tdcfoutput#descr#;/cfoutput/td td#town#/td td#category#/td td#category2#/td /tr /cfoutput /table Thanks in Advance Tim Laureska ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
