How to merge data from multiple tables in SQL
Sample Data
Student - s1, Sid Course - c1, Science Enrollment - 1, s1, c1, 80I 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
精彩评论