开发者

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开发者_Go百科, and in DB.

Any one for the rescue!


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


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


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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜