SQL Query to select names of courses that have prerequisite requirements and name of the prerequisit-CodePudding

Course Table

course_id	course_name
1	s001
2	s002
3	s003
4	s004

Prerequisite Table

course_id	prerequisite_course_id
3	2
4	1

My question is to print both the Names of the course and the Prerequisite Course Name. Example:

course_name	prerequisite_course_name
s003	s002
s004	s001

CodePudding user response：

You can simply use inner join for that:

Schema and insert statements:

 create table course(course_id int, course_name varchar(10));
 insert into course values(1,   's001');
 insert into course values(2,   's002');
 insert into course values(3,   's003');
 insert into course values(4,   's004');
 
 create table Prerequisite(course_id int, prerequisite_course_id int);
 insert into Prerequisite values(3, 2);
 insert into Prerequisite values(4, 1);

Query:

 select c.course_name, pc.course_name
 from Course c inner join Prerequisite p 
               on c.course_id=p.course_id
              inner join Course pc
              on p.prerequisite_course_id=pc.course_id

Output:

course_name	course_name
s004	s001
s003	s002

db<>fiddle here

CodePudding user response：

your data

drop 
  table if exists tmp_CourseTable;
create table tmp_CourseTable (
  course_id int NOT NULL, 
  course_name VARCHAR(40) NOT NULL
);
INSERT INTO tmp_CourseTable(course_id, course_name) 
VALUES 
  (1, 's001'), 
  (2, 's002'), 
  (3, 's003'), 
  (4, 's004');
drop 
  table if exists tmp_PrerequisiteTable;
create table tmp_PrerequisiteTable (
  course_id INTEGER NOT NULL , 
  prerequisite_course_id INTEGER NOT NULL
);
INSERT INTO tmp_PrerequisiteTable(
  course_id, prerequisite_course_id
) 
VALUES 
  (3, 2), 
  (4, 1);

you should join Prerequisite Table with Course Table twice

select 
  a.course_name, 
  b.course_name 
from 
  tmp_PrerequisiteTable p 
  join tmp_CourseTable a on p.course_id = a.course_id 
  join tmp_CourseTable b on p.course_id = b.course_id