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.
Thanks.
--
Swartz