SQLite database with JOIN and SELECT

Question

I have a database problem where basically one course can have many modules AND these modules could be on every single course. So Course 1 could contain module 1 and module 2 but course 2 might only contain module 2. I am hoping to use JSON in the modules table to store timetabling information.

I want to be able to get a list of modules on each course.

What I would want to do is SELECT * from modules where moduleID in (select moduleIDs from courses where courseName = USERSPECIFEDCOURSE)

I want this to return all of the rows in modules table where their ID is matching with the course the user has picked What will this return, is there a better way of doing this?

syllabus · Accepted Answer · 2014-12-05 09:34:44Z

As there is a many-to-many relation between tbl_modules and tbl_Courses, you should remove your moduleIDS column and create a new "relation" table:

tbl_module_course_relations:

courseName             moduleID
Cpting with games      1
Cpting with games      2
Cpting                 1

your query would become:

SELECT * from modules where moduleID in (select moduleID from tbl_module_course_relations where courseName = USERSPECIFEDCOURSE);

Actually it would be better to add "id" columns in each table and rely on it for the "relation" table and the foreign keys.

tbl_courses:

courseID   courseName
1          Cpting with games
2          Cpting

tbl_module_course_relations:

courseID             moduleID
1                    1
1                    2
2                    1

and the query:

SELECT * from modules where moduleID in (select tbl_module_course_relations.moduleID from tbl_module_course_relations, courses where tbl_module_course_relations.courseID = courses.courseID and courses.courseName = USERSPECIFEDCOURSE);

Collectives™ on Stack Overflow

SQLite database with JOIN and SELECT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related