CODEWITHSUNDEEP
sqlsql-serverjoin
Null Head
Null Head

Reputation: 2941

How to merge data from multiple tables in SQL


Guess I am in a complex situation. Heres the scene.
I have 3 tables in SQL Server.
Enrollment, Student, Course. (FYI, there are no foreign keys)
The table columns are
Student - StudentId, StudentName
Course - CourseId, CourseName
Enrollment - EnrollmentId, StudentId, CourseId, CourseResult

Sample Data
Student - s1, Sid
Course - c1, Science
Enrollment - 1, s1, c1, 80

I would want a SQL query that selects data like below

1, s1, Sid, c1, Science, 80

I did it hard way in the DAL layer with multiple calls to database. But would like to do it in one call, and in DB.

Any one for the rescue!

Upvotes: 3

Views: 27589

Answers (4)

Paul Sasik
Paul Sasik

Reputation: 81429

There actually are foreign keys in your data model. They may not be marked as such in the db or you don't realize it. I also think it would become clearer if in your list you put the Enrollment table second, between Student and Course. Enrollment is the table that links a student to a course.

I think you just want an inner join on the three tables like this:

SELECT e.EnrollmentId, s.StudentId, c.CourseId, c.CourseName, e.CourseResult
FROM Student AS s
JOIN Enrollment AS e ON s.StudentId = e.StudentId
JOIN Course AS c on e.CourseId = c.CourseId

Upvotes: 7

Richard Schneider
Richard Schneider

Reputation: 35477

Use a join.

select enrollment.*, student.*, course.* from enrollment
   inner join Student on enrollment.studentId = student.studentId
   inner join Course on enrollment.courseId = course.courseId

Upvotes: 17

dgilland
dgilland

Reputation: 2748

Try:

SELECT e.EnrollmentId, s.StudentId, s.StudentName, c.CourseId, c.CourseName, e.CourseResult
FROM Student s JOIN
Enrollment e ON s.StudentId = e.StudentId JOIN
Course c ON e.CourseId = c.CourseId

Upvotes: 2

Kon
Kon

Reputation: 27431

Seems like a few simple JOINs would do it... 

select 
     e.EnrollmentId 
    ,s.StudentId
    ,s.StudentName
    ,c.CourseId
    ,c.CourseName
    ,e.CourseResult
from Enrollement e
  inner join Course c on e.CourseId = c.CourseId
  inner join Student s on e.StudentId = s.StudentId

Upvotes: 2

Related Questions