Re: Advice for CF Query and Schema
You'd pass the id instead of the title Some Course Title Some Other Course Title - 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 > ANDcoursedate > 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]
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 ANDcoursedate > 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]
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 ANDcoursedate > 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]
Advice for CF Query and Schema
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]
