Help with SQL JOIN Query

[vindesigns]

Hi there,

 

I wonder if you guys could help me please, I'm still learning about PHP SQL so bare with me.

 

In my database I have the following 3 tables: courses, modules and coursemodule

 

courses

 

courseID

courseTitle

courseCode

courseDepartment

courseDescription

 

modules

 

moduleID

moduleTitle

moduleCode

moduleLevel

 

coursemodule

 

ID

courseID

moduleID

iscore

 

Many modules can be associated with many courses

 

When the user selects a course I would like them to see a list of modules associated with the course. At the moment when a course is selected it goes to a new page where the courseID is passed in the URL parameter.

 

How would I write the SQL statement so that the user would be able to see the list of modules (moduleCode, moduleTitle etc) associated with that module providing the courseID is passed in the URL parameter? I believe you write a INNER join but not too sure on how I would go about it.

 

Any ideas?

Thanks

 

 

 

 

[The Little Guy]

Something like this (untested):

 

select * from courses c
left join coursemodule cm on (c.courseID = cm.courseID)
left join modules m on (m.moduleID = cm.moduleID)
where c.courseID = 12345;

 

Replace 12345 with whatever course you are looking for.

[vindesigns]

Thanks for the quick reply,

 

so you would not use an inner join?

 

 

[fenway]

Depends if you want to show courses without any modules.

[vindesigns]

Thanks.