how do you search Right/Left joins?

Question

I am having a problem searching multiple tables ,i have 2 tables

tblcourse

-courseid

-name

-status

tblenroll

-courseid(holds courseid from tblcourse)

-studentid

lets say a student has 1990 as student num and he registered to 2 courses in tblenrol

I want to get the name of the courses that 1990 has and the ones he aint subscribed to

the closest i get is a right outer join to tblcourses then i get the result i want, but once i attach a where clause it wont give me the rest of the courses that havent got his student number.

Any help!!

Answer 1

select tblcourse.name, e.studentid
from tblcourse c left join tblenroll e on c.courseid = e.courseid
where e.studentid is null or  e.studentid is not null and e.studentid = :id

it will result in

course1 1990
course2 NULL
course3 NULL
course4 1990
...

depending on your DB you can use a kind of IIF function to put either 1 or 0, true or false - whatever flag as second element

Answer 2

You probably just need to let the studentid be null as well as the actual id you're querying.

Try, for example:

SELECT tblenroll.studentid, tblcourse.courseid, tblcourse.name, tblcourse.status
FROM tblenroll RIGHT OUTER JOIN tblcourse ON tblenroll.courseid = tblcourse.courseid
WHERE (tblenroll.studentid = 1990 
or tblenroll.studentid is null) -- this allows the courses in which 1990 is not enrolled

Edit: I didn't quite think this through the first time. The above query is my naieve approach and is probably returning the same results that the OP doesn't want. The trick is not to use a where clause after the tables are joined, but rather to limit the rows from tblenroll that participate in the join.

I beleive this should work (I tested it with similar tables/structure on SQL Server 2005):

SELECT tblenroll.studentid, tblcourse.courseid, tblcourse.name, tblcourse.status
FROM tblenroll RIGHT OUTER JOIN tblcourse ON tblenroll.courseid = tblcourse.courseid
    AND tblenroll.studentid = 1990

Answer 3

Hey, thanks for reply. Ok I don't think that I explained it properly.

tblcourse will hold many courses tblenroll holds many enrollments

So, imagine we have 6 courses. Student 1990 registers for 3 and student 1880 for 1 (one that 1990 didn't pick)

When we run this:

WHERE (tblenroll.studentid = 1990 or tblenroll.studentid is null)

or

where e.studentid is null or  e.studentid is not null and e.studentid = 1990

it will pick up 5 courses, 3 registered to 1990 and 2 NULLS. The sixth one is registered to 1880.

This query brings the right results but not for a specific student

SELECT
    tblenroll.studentid as stud,
    tblcourse.name,
    tblenroll.studentid,
    tblenroll.courseid,
    tblcourse.courseid,
FROM
    tblenroll
Right Join tblcourse ON tblenroll.courseid = tblcourse.courseid 

With the above I will get

1880 - 1 1990 - 3 NULL - 2

confusing stuff!

Answer 4

Yeah, that did it, thanks! It's funny, I couldn't find this anywhere. I mean I looked through a few books and they just cover the basics.

Thanks again!! MR PRO