I am sorry, I do have have the query working in Access. What I need to be able to do is not have it return two records which are the same except for the audience name. Perhaps I will have to capture the data in one of the records and then get both names for output in the email. Does this sound like a good idea? My current code is below! Thanks, --Kurt On 5/17/00 7:15 AM, CF-Talk at [EMAIL PROTECTED] wrote: > I would double check your relationships in the db, if those aren't perfect > it can mess up a lot of your data. > > Can you get your query working correctly in Access? If not, then check the > relationships as I mentioned above but if you can, just call the query with > cfstoredproc. It will be a lot faster plus it's cleaner and more reusable. > > Hope that helps, > -Michael > > ======================================== > Michael Imhoff :::: www.omnicypher.com :::: icq: 10495667 > [EMAIL PROTECTED] (home) :::: (320) 255-4447 (home) > [EMAIL PROTECTED] (cell) :::: (612) 247-8159 (cell) > Allaire Alliance Consulting Partner > > > ----- Original Message ----- > From: Kurt Wiersma <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, May 16, 2000 10:57 PM > Subject: Formatting Table Join Output question > > >> Thank you for your help on this query below it is not working under > Access. >> >> I am now trying, with the code below, to output the results of this query > to >> an email. I need the audienceIDs to be joined to the audience name in the >> audience table but of course when I do this under this structure I get two >> records resulting. Both these records are the the same other then then > their >> audience names. I need some way to catch these two audience names out of >> there and display them in the email along with the rest of the email being >> intact. Perhaps I should store them in a local var and then output them? >> >> Anyone have a good way I can accomplish this? >> >> Thanks in advance, >> >> --KW >> >> <CFQUERY NAME="fullentry" DATASOURCE="#application.DataSource#"> >> SELECT calendar.*, audience.*, audevent.eventID, audevent.audienceID >> FROM calendar INNER JOIN (audience INNER JOIN audevent ON >> audience.audienceID=audevent.audienceID) ON >> calendar.eventID=audevent.eventID >> </CFQUERY> >> >> <CFMAIL QUERY="fullentry" MAXROWS=1 TO="#application.emailto#" >> FROM="#contactemail#" SUBJECT="Calendar Entry to be Approved" TYPE="HTML"> > >> This email was generated by fusion.acc.stolaf.edu! >> <P> >> <CFOUTPUT> >> The following calendar entry sent by #contactname# is waiting for > approval. >> <P> >> <B>Event Name:</B> #eventname#<BR> >> <B>Start Date:</B> #DateFormat(startdate, "MM/DD/YYYY")#<BR> >> <B>End Date:</B> #DateFormat(enddate, "MM/DD/YYYY")#<BR> >> <B>Time:</B> #TimeFormat(eventtime, "hh:mm tt")#<BR> >> <B>Location:</B> #location#<BR> >> <B>WebURL:</B> #webURL#<BR> >> <P> >> <B>Audience:</B> #name# >> <P> >> <B>Category:</B> #name# >> <P> >> <B>Description:</B><BR> >> #description# >> >> >> -------------------------------------------------------------------------- > - >> -------------------------- >> <B><A >> > HREF="http://fusion.acc.stolaf.edu/calendar/admin/approve.cfm?id=#eventID#"> >> Approve Entry</A></B> >> <B><A >> > HREF="http://fusion.acc.stolaf.edu/calender/admin/editevent.cfm?id=#eventID# >> ">Edit this Entry</A></B> >> </CFOUTPUT> >> </CFMAIL> >> >> On 5/16/00 5:34 PM, Heather Haindel at [EMAIL PROTECTED] wrote: >> >>> Based on the tables I gave below, I would use this: >>> >>> select e.eventname, e.eventdescription, a.audiencename >>> from test_event e, test_audience_event ae, >>> test_audience a >>> where e.event_id = ae.event_id >>> and ae.audience_id = a.audience_id >>> >>> This works in SQL Server 6.5...you may need to adjust >>> for your needs. >>> >>> I would also consider setting this up as a view (a >>> query in Access) and pulling the data to your web site >>> from there. >>> >>> --- Kurt Wiersma <[EMAIL PROTECTED]> wrote: >>>> >>>> >>>> >>>> What would be the SQL query to join these tables so >>>> that I could display the names >>>> of the audiences for each event? >>>> >>>> Would something like this work? >>>> >>>> <CFQUERY NAME="fullentry" >>>> DATASOURCE="#application.DataSource#"> >>>> SELECT c.eventID, c.startdate, c.enddate, >>>> c.eventname, c.eventtime, >>>> c.location, c.approved, c.webURL, c.description, >>>> c.contactname, c.contactemail >>>> FROM calendar c >>>> WHERE c.eventID IS IN ( SELECT eventID, audienceID >>>> FROM audevent >>>> WHERE audienceID IN (SELECT >>>> audienceID, name FROM audience)) >>>> </CFQUERY> >>>> >>>> ---------- >>>> From: Heather Haindel >>>> Reply-To: [EMAIL PROTECTED] >>>> Date: Mon, 15 May 2000 17:21:47 -0700 (PDT) >>>> To: [EMAIL PROTECTED], >>>> [EMAIL PROTECTED] >>>> Subject: Re: Database Structure Question >>>> >>>> Assuming I understand correctly, you have two >>>> entities...audience and event. Each event on your >>>> calendar can be classified under one to many >>>> audience >>>> (types). Each audience (type) can be a >>>> classification >>>> for zero to many events. That gives you a many to >>>> many >>>> relationship between audience and event. >>>> >>>> In order to normalize this, you need an intersection >>>> table. I would create these three tables: >>>> >>>> audience - audience_id (PK), audiencename >>>> event - event_id (PK), eventname, eventdescription >>>> audience_event - audevent_id (PK), audience_id (FK), >>>> event_id (FK) >>>> >>>> ------------------------------------------------------------------------------ Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
