Hi all.

I'm trying to write a query but cant get it to work right.

I have two tables that I wish to join

table: required_books
Columns: class_id, isbn
----------------
x | 1
x | 2
y | 1


table: books
columns: isbn, author, title, ... etc (irrelevant)
----------
1 | J. Bloe | Mysql | ...
2 | H. Simpson | Diary | ...
3 | H. Simpson | The life of Bart | ...


What I'm trying to get is books that a particular class hasn't already
requested.
So for class_id 'x' , the query would return isbn id of 3, for class_id 'y',
i should get books with book_isbn id's 2 and 3.

This is what i'm trying to do for class_id 'x':
SELECT books.isbn
FROM required_books AS rb
RIGHT OUTER JOIN books ON books.isbn = rb.isbn
WHERE rb.crn_id != 'x' OR rb.isbn IS NULL

Which works when there's only single class entries in required_books table,
like this
---------
x | 1
x | 2

But as soon as you get varying class_id's, it breaks...

I'd really appreciate if someone could give me a hint as to how I should fix
the query so that it works properly. Oh I'm using MySQL v3.23.58 if anybody
wants to know.

you have to make the join go looking for the 'x' rows

then test for null, meaning that book doesn't have an 'x' match


p.s. sorry, i always rewrite them as LEFT OUTER

i just cannot get my head around RIGHT OUTER

It now works!
Thank you!