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.
