1

Suppose I have this database

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Roll INT NOT NULL,
    CustomID INT UNIQUE
);


INSERT INTO Students (StudentID, Name, Roll, CustomID)
VALUES
    (1, 'John Doe', 101, 1),
    (2, 'Jane Smith', 102, 2),
    (3, 'Bob Johnson', 103, 3);


CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    ClassName VARCHAR(255) NOT NULL,
    CustomID INT UNIQUE,
    FOREIGN KEY (CustomID) REFERENCES Students(CustomID)
);


INSERT INTO Classes (ClassID, ClassName, CustomID)
VALUES
    (1, 'Class 10', 1),
    (2, 'Class 11', 2),
    (3, 'Class 12', 3);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(255) NOT NULL,
    CustomID INT,
    FOREIGN KEY (CustomID) REFERENCES Students(CustomID)
);


INSERT INTO Courses (CourseID, CourseName, CustomID)
VALUES
    (101, 'Mathematics', 1),
    (102, 'Physics', 1),
    (103, 'Chemistry', 1),
    (104, 'Literature', 2),
    (105, 'History', 2),
    (106, 'Biology', 3),
    (107, 'Computer Science', 3);


CREATE TABLE StudentCourses (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

INSERT INTO StudentCourses (EnrollmentID, StudentID, CourseID)
VALUES
    (1, 1, 101),
    (2, 1, 102),
    (3, 1, 103),
    (4, 2, 104),
    (5, 2, 105),
    (6, 3, 106),
    (7, 3, 107);

CREATE VIEW StudentInformation AS
SELECT
    Students.StudentID,
    Students.Name AS StudentName,
    Students.Roll,
    Classes.ClassName,
    STRING_AGG(Courses.CourseName, ', ') AS EnrolledCourses
FROM
    Students
JOIN
    Classes ON Students.CustomID = Classes.CustomID
LEFT JOIN
    StudentCourses ON Students.StudentID = StudentCourses.StudentID
LEFT JOIN
    Courses ON StudentCourses.CourseID = Courses.CourseID
GROUP BY
    Students.StudentID, Students.Name, Students.Roll, Classes.ClassName;

If I create a view, I get this output

StudentID StudentName Roll ClassName EnrolledCourses
1 1 John Doe 101 Class 10 Mathematics, Physics, Chemistry
2 2 Jane Smith 102 Class 11 Literature, History
3 3 Bob Johnson 103 Class 12 Biology, Computer Science

Now suppose I have a grid where I provide this view data for displaying, also the grid can provide me filter options like Roll greater than X, Name starts with 'Y'. For example, the grid can provide me query like, 'WHERE StudentName = 'A', the grid filter will provide me only the part of the WHERE clause.

I want to perform another query after the filter, using my previous view create query and the filter query provided by the grid. So it can look this,

'SELECT * ..... from Studente WHERE StudentName = 'A'....' . But here StudentName is aliased which should be Students.Name.

Is there any way to keep track of the column name without managing a separate table to map the column names and aliases? Also, I can't use Select ... from StudentInformation'. The view is not directly passed to the grid.

2
  • SQL Server (MS SQL) and MySQL are two different DBMSs. Check what DBMS do you use and remove wrong tag/
    – Akina
    Jan 15 at 6:00
  • Looks like SQL Server in which case INFORMATION_SCHEMA.COLUMNS also tracks views Jan 15 at 12:40

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.