CODEWITHSUNDEEP
sql-serverjoininner-joinouter-join
rbk
rbk

Reputation: 283

SQL Views Table Joins Confusion

So,

I have 3 tables with me.

Users ( Id, FirstName, LastName )
Courses ( CourseId, CourseTitle, Faculty)
CourseEnrollment( StudentId, CourseId , FinalGrade )

Now, I am supposed to: Create a view with
a. Student First Name
b. Student Last Name
c. Course Title
d. Faculty First Name
e. Faculty Last Name
f. Final Grade

Since Student and Faculty names are all stored in one table ( Users) I decided to make a temp View of student First Name, Student Last Name and Course Title. 

CREATE VIEW FirstView ([Student First Name],[Student Last Name],[Course Title])
    AS  

        SELECT Users.FirstName,Users.LastName, Courses.CourseTitle
                FROM Users
                INNER JOIN CourseEnrollment
                        ON CourseEnrollment.StudentId=Users.NTID
                INNER JOIN Courses
                        ON Courses.CourseId=CourseEnrollment.CourseId;

For doing the main work now..I'm creating a new view like this:

SELECT FirstView.[Student First Name],FirstView.[Student Last Name],FirstView.[Course Title], Users.FirstName, Users.LastName, CourseEnrollment.FinalGrade
                FROM FirstView
                 JOIN Courses
                        ON Courses.CourseTitle LIKE FirstView.[Course Title]  -- only those courses which are there in first view
                 JOIN Users
                        ON Users.NTID LIKE Courses.Faculty   -- only the faculty users added
                 INNER JOIN CourseEnrollment
                        ON CourseEnrollment.CourseId LIKE Courses.CourseId;

Problem is...if I don't add the final grade column and Join CourseEnrollment...it works fine. But as soon I do this...it's making a mess and making multiple rows for each student. With grades from other students.

Anyone have any idea how I can work around this ?

Upvotes: 0

Views: 100

Answers (1)

jpw
jpw

Reputation: 44881

If the facultyfield in the courses table refer to a user maybe this is what you want:

SQL Fiddle

MS SQL Server 2012 Schema Setup:

CREATE TABLE Users ( Id int , FirstName varchar(20), LastName varchar(20))
CREATE TABLE Courses ( CourseId int , CourseTitle varchar(20), Faculty int)
CREATE TABLE CourseEnrollment ( StudentId int, CourseId int, FinalGrade int)

INSERT Users VALUES (1, 'John', 'Adams')
INSERT Users VALUES (2, 'Steve', 'Student')
INSERT Users VALUES (3, 'Fred', 'Faculty')
INSERT Courses VALUES (1, 'DB 101', 3)
INSERT Courses VALUES (2, 'DB 201', 3)
INSERT CourseEnrollment VALUES (1,1,30)
INSERT CourseEnrollment VALUES (2,1,50)
INSERT CourseEnrollment VALUES (2,2,75)

Query 1:

SELECT
    u.FirstName AS [Student FirstName], u.LastName as [Student LastName],
    c.CourseTitle,
    faculty.FirstName AS [Faculty FirstName],
    faculty.LastName AS [Faculty LastName],
    ce.FinalGrade
FROM Users u
INNER JOIN CourseEnrollment ce ON u.Id = ce.StudentId
INNER JOIN Courses c ON c.CourseId = ce.CourseId
INNER JOIN Users faculty ON c.Faculty = faculty.Id

Results:

| STUDENT FIRSTNAME | STUDENT LASTNAME | COURSETITLE | FACULTY FIRSTNAME | FACULTY LASTNAME | FINALGRADE |
|-------------------|------------------|-------------|-------------------|------------------|------------|
|              John |            Adams |      DB 101 |              Fred |          Faculty |         30 |
|             Steve |          Student |      DB 101 |              Fred |          Faculty |         50 |
|             Steve |          Student |      DB 201 |              Fred |          Faculty |         75 |

Upvotes: 1

Related Questions