December 7th, 2011, 08:27 AM
Query help please
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
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
December 7th, 2011, 10:46 AM
Try something like this:
Originally Posted by lolittle
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
FROM rental_line rl
WHERE rl.book_id = b.book_id);
December 7th, 2011, 10:53 AM
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
SELECT BOOK_TITLE FROM BOOK_CATALOGUE
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;
December 15th, 2011, 04:09 AM
I am not sure whether it is necessary to use catalog table or not.
FROM BOOK B
WHERE NOT EXISTS (SELECT 1 FROM RENTALS R WHERE R.BOOK_ID=B.BOOK_ID)