Having trouble with joins.
I have a table called
subno subname 30006 Math 31445 Science 31567 Business
I also have a another table called
subno sno 30009 980008 4134 988880
how to list subject numbers and subject names for student 9800007 ?
select * from subjects s left join enrollment e on s.subno = e.subno where sno=9800007
If you want to return zero rows for students without an enrolment, use a
LEFT [OUTER] JOIN, eg:
SELECT e.sno, s.subno, s.subname FROM enrollment e LEFT OUTER JOIN subjects s ON s.subno = e.subno WHERE e.sno=988880;
To return no rows for students without enrolments, use an
SELECT e.sno, s.subno, s.subname FROM enrollment e INNER JOIN subjects s ON s.subno = e.subno WHERE e.sno=988880;
Note that join order is important for outer joins (
RIGHT [OUTER] JOIN and
LEFT [OUTER] JOIN - the
OUTER keyword is optional) but not for
INNER JOIN. For that reason, @swetha's answer has a problem: the join order is reversed if you're looking for information about a student.
See this SQLFiddle