CODEWITHSUNDEEP
mysqlsql
dsquaredtech
dsquaredtech

Reputation: 89

SQL Outer joins

Three tables courses,registration,students

columns in students

firstname,lastname,studentid,major,admitdate,graddate,gender,dob

columns in registration

courseid,studentid

columns in courses

coursenumber,coursename,credits

select statement I need to modify

select lastname as 'Last Name',sum(credits) as 'Credits Registered For' from students   as s
inner join registration as r on s.studentid = r.studentid
inner join courses as c on c.coursenumber = c.courseid
group by last name;

the question on the lab is... Modify the previous query to show all students, even if they have not registered for a class. You should have 14 rows. Students who are not registered will show NULL in output.

I know this requires outer join of some sort but I'm not fully grasping these joins i've read multiple posts on here and other sites but can't seem figure it out.

Upvotes: 0

Views: 56

Answers (2)

John Woo
John Woo

Reputation: 263893

use LEFT JOIN

select  lastname as 'Last Name',
        sum(credits) as 'Credits Registered For' 
from    students   as s
        LEFT join registration as r on s.studentid = r.studentid
        LEFT join courses as c on c.coursenumber = r.courseid
group by last name;

Upvotes: 3

xkeshav
xkeshav

Reputation: 54072

try 

SELECT  s.lastname AS 'Last Name',
        sum(c.credits) AS 'Credits Registered For' 
FROM    students    s
        LEFT JOIN registration  r ON (s.studentid = r.studentid)
        INNER JOIN courses  c ON (c.coursenumber = r.courseid)
GROUP BY lastname;

note: no need to use AS while giving alias to table name

Upvotes: 0

Related Questions