Need help with basic table Question

[igirl]

I have two tables but I'm thinking it should be one.

one is:
Courses
columns are:
CourseID, Title, Description, Objectives, Price, Length, Prerequisites

the other is:
CourseDetails
columns are:
CourseID (where this is checked against the Courses table, must exist in the Courses table), CategoryID, TypeID, SubTypeID, Type

First off I'm a designer not a developer. My question is should I have made this one table? I think I created it this way in case one course had more than one detail. But I realize one course has one CategoryID and TypeID etc. Anyway I have built a site using this db (small db, 200 items) So I probably should keep as is.

I have been inputting new items in the Courses table and then inputting them in the CoursesDetails table.

If I keep both tables how can I add the Title to the CourseDetails table without manually adding them all? All the CourseIDs are in but I want to add the Titles now, the Title column is only in the Courses column. Also in the future how can I update the CourseID, and Title columns in both the Courses table and the CourseDetails table?

Any help is greatly appreciated!

[igirl]

Now I accidentally deleted all titles on a live site! How can I input titles in both columns and make sure they are input in accordance with the courseID?

[darkagn]

Hi igirl and welcome to DaniWeb :)

If I keep both tables how can I add the Title to the CourseDetails table without manually adding them all?

There is no need, it is in the Courses table so you don't need it in the CourseDetails table as well. To select all details from both tables you do a join like so:

select Courses.CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type
from Courses
inner join CourseDetails
ON Courses.CourseId = CourseDetails.CourseId

Now for your second problem, all you need to do is update the Courses table according to the CourseId like so:

update Courses set Title = 'Course Title' where CourseId = 12

Just change 'Course Title' and 12 to match the record that you are updating.

Hope this helps.

[igirl]

I'll look into it asap. Thanks for the reply. I'll post back asap!

[igirl]

How can I make the first query into one table?

[darkagn]

How can I make the first query into one table?

What do you mean? What are you trying to accomplish?

[igirl]

It's kind of on hold now but I wanted to create a table that is exactly like the query which joins two table together. I want a table created out of two. But I'm assuming I have to use UPDATE or INPUT.

The following doesn't work:

UPDATE Courses
SET Courses.CourseID = tblCourseDetails.CourseID

FROM Courses INNER JOIN tblCourseDetails
ON Courses.CourseID = tblCourseDetails.CourseID

I have to get on another project and have to learn more later but if you can help I will appreciate!

[apegram]

If you created the new table to match the fields of the query exactly, so you would have a new table something like this

Table: CoursesNew
Columns: CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type

Then you could modify the SQL statement provided by darkagn to insert the results of the query into your new table.

Insert Into CoursesNew (CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type)
Select Courses.CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type
from Courses
inner join CourseDetails
ON Courses.CourseId = CourseDetails.CourseId

[igirl]

If you created the new table to match the fields of the query exactly, so you would have a new table something like this

Table: CoursesNew
Columns: CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type

Then you could modify the SQL statement provided by darkagn to insert the results of the query into your new table.

Insert Into CoursesNew (CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type)
Select Courses.CourseID, Title, Description, Objectives, Price, Length, Prerequisites, CategoryID, TypeID, SubTypeID, Type
from Courses
inner join CourseDetails
ON Courses.CourseId = CourseDetails.CourseId

Thank you mucho for this! I will test it asap. Thanks!