implementation - transact sql - many to many relationship

enrique_aeo
enrique_aeo used Ask the Experts™
on
Hello, What is the implementation?
tblCourse(CourseID, CourseName, CourseField1, CourseField2...)
tblStudent(StudentID, FamilyName, FirstName, DoB, StudentField1, StudentField2...)
tblCourseStudent(CourseID, StudentID, PerformanceMetric1, PerformanceMetric2...)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
enrique_aeo said:
>>Hello, What is the implementation?

What is your question?  What are you trying to accomplish?

Quick note: that table structure looks denormalized...

Author

Commented:
THE  CODE transact sql
Top Expert 2010

Commented:
enrique_aeo,

Sorry, but it is still not clear.

Patrick
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
tblCourse(CourseID, CourseName, --) when CourseID is primary key
tblStudent(StudentID, FamilyName, ..) when StudentID is primary key
tblCourseStudent(CourseID, StudentID, ...) when CourseID and StudentID are foreign key
something like I do in transact sql code?

Author

Commented:
my question is, as it resolves a many to many relationship in transact sql code?
Commented:
Your last table needs to look like this:

tblCoursesStudents
==============
CrsStuID - primary key - autonumber
CourseID - foreign key from tblCourses
StudentID - foreign key from tblStudents
PerformanceMetric1 - datatype as required
PerformanceMetric2 - datatype as required
etc.

I always pluralize tables - it is so much more natural - notwithstanding some pundits!  tblStudent sounds like info about only one student.

Commented:
There is no code to form the third table.  You should create a form with combo boxes for both Courses and Students as well as some way of entering all the performance metrics (combo or list box)

Commented:
Try:
SELECT 
	 C.*
	,CS.*
	,S.*
FROM
	tblCourse C
INNER JOIN
	tblCourseStudent CS ON C.CourseID = CS.CourseID
INNER JOIN
	tblStudent S ON CS.StudentID = CS.StudentID

Open in new window

Commented:
tblCoursesStudents is the Joining table between the two Many to Many tables allowing a one-to-many

tblCourses M-----> tblCoursesStudents <------M tblStudents

Author

Commented:
tblCourses and tblStudents are an eschema Intranet, these tables already exist. The new table is tblCoursesStudents,  therefore would only have to make the code table creating tblCoursesStudents or I do something extra in the tables tblCourses and tblStudents

Commented:
Somehow you have to get the info about what students are taking what course into the the tblCoursesStudents.  As I suggested earlier, the best way would be to use a form designed for that purpose.  You have to create the table and then use the form to populate it.

Commented:
Thanks, glad to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial