CODEWITHSUNDEEP
sqlsql-serverjoin
paratrooper
paratrooper

Reputation: 465

SQL Join Query for 3 tables not working

I'm new to SQL and need some help. I have the following 3 tables Tables in my database.

STUDENT Table whose tuple has the following format:
{StuID, LastName, FirstName, MiddleName, EmailID}

COURSES Table whose tuple has the following format:
{CourseID, CourseName, CreditHours, CourseDescription}

TAKEN_COURSES Table whose tuple has the following format:
{ID, StuID, CourseID, Semester, Year}.

Now I want to display all courses taken by students in the following format:

LastName FirstName MiddleName Course1   Course2
May         Alice    Mary      Math     Science
Wood        Bob      Adams     Physics  History

It is known in advance each student can take atmost 2 courses only. If a student takes just 1 course, the other entry can be blank.

I joined the 3 tables as:

Select Student Student.LName, Student.FName, Student.MiddleName, Courses.CourseName from Student INNER JOIN (Courses INNERJOIN TakenCourses ON Courses.CourseID=TakenCourses.CourseID) ON Student.StuID = TakenCourses.CourseID

This gives me the following:

LastName FirstName MiddleName CourseName
May         Alice    Mary      Math     
May         Alice    Mary      Science
Wood        Bob      Adams     Physics  
Wood        Bob      Adams     History

But I do not want that. I want all courses to show up one after the other in a single tuple and I'm really struggling to achieve that. Any suggestions about what needs to be done?

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can do what you want with aggregation, specifically because there are two values. Your query is most of the way there:

Select s.LName, s.FName, s.MiddleName, 
       min(c.CourseName) as course1,
       (case when min(c.CourseName) <> max(c.CourseName) then max(c.CourseName)
        end) as course2
from Student s join
     TakenCourses tc
     on s.StuID = tc.CourseID
     Courses c 
     on c.CourseID = tc.CourseID
group by s.LName, s.FName, s.MiddleName;

Why is aggregation useful? Well, you have multiple rows per student and you want only one. In such cases, group by is often the right approach.

Also note the use of table aliases, which simplify writing and reading the query. Also, it is best to avoid parentheses in the from clause, when they are not necessary.

Upvotes: 2

Related Questions