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)
> >>
> >>
> >> --- Kurt Wiersma wrote:
> >>>
> >>> I have a database which is going to hold events
> >> for
> >>> a calendar app. In it I
> >>> have a table for audiences which has an audienceID
> >>> and audience name. Inside
> >>> the calendar table I have a field for audienceIDs
> >>> where I was thinking of
> >>> keeping a comma delimited list of audienceIDs.
> >>>
> >>> The problem is I cannot join the two tables very
> >>> easily in this format. I
> >>> want the user to be able to check a checkbox for
> >>> each audience they would
> >>> like the event to be categorized under.
> >>>
> >>> Can anyone suggest a better database structure or
> >>> code that would allow the
> >>> two tables to be joined under this structure?
> >> Please
> >>> cc my email address in
> >>> the reply as I am on the digest format right now!
> >>>
> >>> Thanks for you help,
> >>>
> >>> -- Kurt
> >>>
> >>>
>
> --------------------------------------------------------------------------
----
> 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.
------------------------------------------------------------------------------
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.
