The query below works if you take out the "test"
part...that was to test it against an existing SQL
database...
I tested this same setup in Access 97 and it worked
fine. You will need to set up your relationships in
Access. I've got one to many/enforce referential
integrity set up between event and audience_event and
audience and audience_event.
Here's the query:
select e.eventname, e.eventdescription, a.audiencename
from event e, audience_event ae, audience a
where e.event_id = ae.event_id
and ae.audience_id = a.audience_id
I would display this with something to the effect of
"for each eventname, show a list of audiences."
I'll email you the database separately.
--- Kurt Wiersma <[EMAIL PROTECTED]> wrote:
> 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.
>
__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
------------------------------------------------------------------------------
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.
