Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Unanswered: Help: join queries

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

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    PHP Code:
    select books.isbn
      from books
    left outer
      join required_books 
    as rb
        on books
    .isbn rb.isbn
    and              rb.crn_id 'x'
    where rb.isbn is null 
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2002
    It now works!
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts