CODEWITHSUNDEEP
mysqlsubquery
Gajen Dissanayake
Gajen Dissanayake

Reputation: 103

Select multiple values from a column based on a certain value from another column

I have three mysql tables as "Courses", "Modules", and "Course_Managements" as following...

"Modules" Table...

Module_id   Module_title
  1         Accounting
  2         Management
  3         Marketing

"Courses" Table...

Course_id   Course_title
  1          CIMA
  2          aat
  3          SLIM
  4          MBA

"Course_Managements" Table...

 CM_id   Module_id  Course_id
  1           1         1
  2           1         2
  3           2         1
  4           2         4
  5           3         3
  6           3         4

I want to assign SQL Query, by selecting a Course ID (Let's say '1') then the available Module_ids have to be listed as... '1' and '2'

I'm trying to develop an interface by which a User can select a Course then he / she can view the Modules under it...

Could anyone suggest a SQL Query to do it..?

Upvotes: 0

Views: 61

Answers (2)

Ullas
Ullas

Reputation: 11556

Query

select t2.Course_id,
t2.Course_title,
group_concat(t1.Module_title separator ',') as Modules
from modules t1
join courses t2
join course_management t3
on t1.Module_id=t3.Module_id
and t2.Course_id=t3.Course_id
where t2.Course_id=1
group by t2.Course_id,
t2.Course_title;

Fiddle demo here

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

You have to take input of courseId from User and use following query to get modules.

SELECT *
FROM Modules
INNER JOIN Course_Managements ON Modules.Module_id = Course_Managements.Module_id
WHERE Course_Managements.Course_id = $course_id

Upvotes: 1

Related Questions