<select name="courseid" size="1">
<option value="1">Some Course Title</option>
<option value="2">Some Other Course Title</option>
</select>
----- Original Message -----
From: "Ian Vaughan" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, July 14, 2004 8:20 AM
Subject: RE: Advice for CF Query and Schema
> Deanna
>
> Ok thanks, when adding a new course to the iteccourse database, how
> would I get the correct value to place into the courseoutlineID (foreign
> key to courseoutline) field ????
>
> Because when adding a new course to the iteccourse database, the user
> would have to select the course title from a form select list otherwise
> the admin user would not know what course they are adding ???
>
> ________________________________
>
> From: Deanna Schneider [mailto:[EMAIL PROTECTED]
> Sent: 14 July 2004 12:45
> To: CF-Talk
> Subject: Re: Advice for CF Query and Schema
>
>
> Ian,
> You need to have a field (a foreign key) in your second table that
> corresponds to the info in your first table.
>
> So, your "courseoutline" table looks like so:
> ID (primary key)
> Title
> Summary
> Owner
> Coursepath
>
> Then, your "iteccource" looks like so:
> ID (primary key)
> courseoutlineID (foreign key to courseoutline)
> coursedate
> duration
> availability
> owner
>
> To join them, it'd be a simple query:
>
> SELECT o.title, o.summary, o.coursepath, i.coursedate, i.duration,
> i.availability
> FROM outlinecourse o, iteccourse i
> WHERE o.id = i.courseoutlineid
> AND coursedate > sysdate
>
> (That last line is going to vary depending on your database platform, so
> consider that pseudocode. Also, if you're using something that supports
> the
> standard inner join syntax, you might want to use that instead of my
> version, which works in Oracle.)
>
> You don't need to repeat the title in the iteccourse table, as it's
> available in the courseoutline table.
>
> ----- Original Message -----
> From: "Ian Vaughan"
>
> > I am trying to design two databases that share a relationship that
> > contains information for online training courses.
> >
> > So in the first table courseoutline they would use a html form to add
> > the course information to the db such as
> >
> > ID = 1
> > Course Title =i.e. Windows 2000
> > Course Summary = Summary of Course in this field
> > Owner = automatically populated through session name variables.
> > Coursepath - contains word doc containing course contents uploaded via
> > cffile
> >
> > In the second table iteccourse they would use a form to add the course
> > dates and availability to the db such as
> >
> > CourseID = 1
> > Course Title = Selected from form select field populated using CF
> with
> > entries from the course title field in the courseoutline db.
> > Coursedate = 17 July 2004
> > Duration = 3 Days
> > Availability = Yes/ Full
> > Owner = automatically populated through session name variables.
> >
> >
> > Then in the detail page of the training site what coldfusion query
> would
> > be needed so that if I just wanted to view upcoming Windows 2000
> courses
> > it would bring back the related coursepath for that course ??
> >
> > Any ideas on the best way to achieve this - or could my table schema
> be
> > improved ??
> >
> > I appreciate your views and help
> >
> >
> >
> >
> >
> >
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
