#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    29
    Rep Power
    0

    Query help please


    Using sqlplus

    i have a rental table, book table and catalogue table

    the rental table has the book id

    the book table has several books by unique book id and also catalogue id

    the catalogue table holds the information of the book like title author price etc.

    there can be several books with the same catalogue id so there can be more than one of the same book .

    my query i need to find out which books do not exist in the renatl table ..

    This is my attempt

    SQL Code:
    SELECT DISTINCT C.BOOK_TITLE 
    FROM BOOK_CATALOGUE C 
    INNER JOIN BOOK B ON C.BOOK_CATALOGUE_ID = B.BOOK_CATALOGUE_ID 
    LEFT OUTER JOIN RENTAL_LINE RL ON B.BOOK_ID = RL.BOOK_ID
    WHERE RL.RENTAL_LINE_ID IS NULL;


    Now the problem is because i have so many books with the same catalogue_id .. if one of those books is not in the rental table it stills returns the book title

    i need a way of making it so if 1 copy has been rented and 1 hasnt but they both have the same catalogue id it should not display the book title

    Hope someone can help

    Thanks
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387

    Talking


    Originally Posted by lolittle
    ... Etc ...
    my query i need to find out which books do not exist in the renatl table ..
    Try something like this:

    Code:
    SELECT DISTINCT c.book_title
      FROM book b 
     INNER JOIN book_catalogue c
        ON c.book_catalogue_id = b.book_catalogue_id
     WHERE NOT EXISTS
            (SELECT '?'
             FROM rental_line rl
             WHERE rl.book_id = b.book_id);
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    29
    Rep Power
    0
    returns all books again .. thanks

    The problem is that the there are several copies of a book just with different id's these are kept in the book table.

    If one is rented and one is not then it will find the one that is not and give me its title .. i need it to not do that if one copy has been rented

    SOLVED!

    SQL Code:
     
    SELECT BOOK_TITLE FROM BOOK_CATALOGUE
    MINUS
    SELECT DISTINCT C.BOOK_TITLE FROM RENTAL_LINE RL INNER JOIN BOOK B ON RL.BOOK_ID = B.BOOK_ID
    INNER JOIN BOOK_CATALOGUE C ON B.BOOK_CATALOGUE_ID = C.BOOK_CATALOGUE_ID;


  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    1
    Rep Power
    0
    I am not sure whether it is necessary to use catalog table or not.
    Try this

    SELECT B.BOOK_TITLE
    FROM BOOK B
    WHERE NOT EXISTS (SELECT 1 FROM RENTALS R WHERE R.BOOK_ID=B.BOOK_ID)

IMN logo majestic logo threadwatch logo seochat tools logo