Al... thanks for the response... I need to show my ignorance.... what is a mapping table?
-----Original Message----- From: Everett, Al [mailto:AEverett@;askallied.com] Sent: Friday, November 08, 2002 8:27 AM To: CF-Talk Subject: RE: help with output from 2 tables The key, I believe, is to outer join your category table twice: <cfquery datasource="calendar" name="findcal"> SELECT e.category1, e.category2, c1.category_no as cat_no1, c1.descr as descr1, c2.category_no as cat_no2, c2.descr as descr2 FROM category c2 RIGHT OUTER JOIN events e ON c2.cat_no = e.category1 LEFT OUTER JOIN category c1 ON e.category2 = c1.cat_no where (e.year=#year# or e.firstyear=#year#) and (e.category1 is not null OR e.category2 is not null) order by month, day </cfquery> This should select all events that have one or both category columns defined. Then in your output, put in decision logic so you display the description if it exists (e.g., Len(Trim(findcal.descr1)) GT 0). Of course, it would have been better to use a mapping table between the two. If you get to more than two categories I strongly suggest it. > -----Original Message----- > From: Tim Laureska [mailto:hometeam@;goeaston.net] > Sent: Thursday, November 07, 2002 8:57 PM > To: CF-Talk > Subject: help with output from 2 tables > > > I'm not sure if this is a database relationship/join question or a cf > question, but here goes... I have an events database with 2 tables > (categories and events). > > The categories table has 2 fields . "cat_no" (number) and > "descr" (text) > > The events table has many fields including category1 and category2 (an > event can have one OR two categories such as "board meeting" and/or > "product shows"). Entries into the Category1 and Category2 fields are > numbers that correspond to the cat_no field in the categories table. > > I'm struggling with getting an event with two categories to display > those two category descriptions. it's no big deal to get the category > numbers to show, but if I try to output the descriptions (descr field > from the categories table) I only get the first description. > the related > code excerpt is below (I hope this makes some kinda sense) > > <cfquery datasource="calendar" name="findcal"> > SELECT e.category1, e.category2, c.category_no, c.descr .etc. > FROM events e, category c > where (e.year=#year# or e.firstyear=#year#) and > (e.category=c.cat_no or > e.category2=c.cat_no) > order by month, day > </cfquery> > > <cfoutput query="findcal" group="event_no"> > category: #descr# > </cfoutput> > > Tim > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 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
