How create procedure to retrieve data from three tables with user inserted data

manpreet Best Answer 2 years ago

I have three tables DEPARTMENTS, SEMESTER and SUBJECTS

CREATE TABLE DEPARTMENTS
(
    D_ID INT PRIMARY KEY identity(1, 1),
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE SEMESTER
(
    D_ID INT FOREIGN KEY REFERENCES departments(D_ID),
    sem_id INT PRIMARY KEY identity(1, 1),
    semester INT CHECK
        (semester BETWEEN 1 AND 8) NOT NULL
);

CREATE TABLE SUBJECTS
(
    D_ID INT FOREIGN KEY REFERENCES departments(D_ID),
    sem_id INT FOREIGN KEY REFERENCES semester(sem_id),
    sub_id INT PRIMARY KEY identity(1, 1),
    sub_name VARCHAR(50),
    syllabus VARBINARY(MAX),
    exam_format VARBINARY(MAX)
);

If user insert department_name in DEPARTMENTS table and semester from semester table then how to get sub_name from subject

manpreet 2 years ago

Select Sub_Name
from Subjects as sbj
inner join Departments as dpt on sbj.D_Id = dpt.D_Id
inner join Semester as smt on sbj.sem_id = smt.sem_id
where (YOUR CRITERIA)

inner join Departments as dpt on smt.D_Id = dpt.D_Id