Creating View + Query (Combining columns + adding an extra attribute)

sql, view

Solution

The working solution you posted looks great. I would just make the plain UNION into a UNION ALL, for it seems unlikely for you to need to remove duplicates from between these 2 subqueries. The ALL will prevent the server from doing unnecessary work to resort the combined results and search for non-existant duplicates.

So it would become:

CREATE VIEW Registrations AS  
(
    SELECT S.identificationnumber AS StudentId, S.name AS StudentName, C.code AS CourseCode, C.name AS CourseName, 'Waiting' AS Status  
    FROM  Waitinglist W, Student S, Course C  
    WHERE S.identificationnumber = W.identificationnumber  
    AND W.code = C.code  
)  
UNION ALL  
(  
    SELECT S.identificationnumber AS StudentId, S.name AS StudentName, C.code AS CourseCode, C.name AS CourseName, 'Registered' AS Status  
    FROM  Registeredat R, Student S, Course C  
    WHERE S.identificationnumber = R.identificationnumber  
    AND R.code = C.code  
);

Problem

I managed to solve it using UNION between two querys, I belive my attempt was a little off and tried to do a mathematical add. This is problaby not the best way you can do it, but it worked and that's enough for me. Thank you for your help. Working solution: ``` CREATE VIEW Registrations AS (SELECT S.identificationnumber AS StudentId, S.name AS StudentName, C.code AS CourseCode, C.name AS CourseName, 'Waiting' AS Status FROM Waitinglist W, Student S, Course C WHERE S.identificationnumber = W.identificationnumber AND W.code = C.code) UNION (SELECT S.identificationnumber AS StudentId, S.name AS StudentName, C.code AS CourseCode, C.name AS CourseName, 'Registered' AS Status FROM Registeredat R, Student S, Course C WHERE S.identificationnumber = R.identificationnumber AND R.code = C.code); ``` Origianl Problem: I'm a begginner at databases and SQL, so things might not look that professional. What I'm trying to do in plain text: I'm trying to create a view for all registerd and waiting students, for all courses. I also want to add a new "column" thats either "registerd" or "waiting". How I want the view to look: ``` StudentID, StudentName, CourseCode, CourseName, Status StudentID = Combined idenficationnumber for Table "RegisterdAt" and "Waitinglist" StudentName = Based on StudentID find matching name in Table "Student" CourseCode = Combined code for Table "RegisterdAt" and "Waitinglist" CourseName = based on code find matching name in Table "Course" Status = Either "registered" or "waiting" depending on if we got the "row" from Table "RegisterdAt" or "Waitinglist" ``` The Created Tables(I have also added some examplery data into them, for easier testing): ``` CREATE TABLE Student( identificationnumber VARCHAR(20), name VARCHAR(50), branchname VARCHAR(50), programmename VARCHAR(50), PRIMARY KEY(identificationnumber), FOREIGN KEY(branchname, programmename) REFERENCES Branch(name, programmename) ); CREATE TABLE Course( code CHAR(6), name VARCHAR(50), credits VARCHAR(10), departmentname VARCHAR(50), PRIMARY KEY(code), FOREIGN KEY(departmentname) REFERENCES Department(name) ); CREATE TABLE Waitinglist( identificationnumber VARCHAR(20), code CHAR(6), ddate VARCHAR(10), PRIMARY KEY(identificationnumber, code), FOREIGN KEY(identificationnumber) REFERENCES Student(identificationnumber), FOREIGN KEY(code) REFERENCES Course_with_maxstudents(code) ); CREATE TABLE Registeredat( identificationnumber VARCHAR(20), code CHAR(6), PRIMARY KEY(identificationnumber,code), FOREIGN KEY(identificationnumber) REFERENCES Student(identificationnumber), FOREIGN KEY(code) REFERENCES Course(code) ); ``` An attempt to create a view(not working, and missing registerd/waiting attribute): ``` CREATE VIEW Registrations AS SELECT (R.identificationnumber + W.identificationnumber) AS StudentId, S.name AS StudentName, (R.code + W.code) AS CourseCode, C.name as CourseName FROM Registeredat R, Waitinglist W, Student S, Course C WHERE S.identificationnumber = (R.identificationnumber + W.identificationnumber) AND C.code = (R.code + W.code); ```

Original source