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]
