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