Search code examples
javasqlnamed-query

In an SQL statement, how do you select all elements that are NOT a certain condition?

I'm sorry I formulated the question so badly--I'm new to programming.

What I'm doing is, I'm using Java @NamedQuery in a little Library manager application. I have the entity class Book with attributes bookid, title, author, etc. and Bookstaken with attributes id, clientid, bookid, datetaken. So Bookstaken.bookid is the foreign key that refers to Book.bookid. Bookstaken contains all the books that have been taken, and Book contains all the books that the library owns (but does not necessarily have at the moment).

I have fetched all Books that have been taken with this Named Query:

@NamedQuery(name = "Taken", query = "SELECT b FROM Book b, Bookstaken t WHERE b = t.bookid")

But now I want to make a Named Query called "Not taken" which will retrieve all books that have... you know, not been taken. So essentially the complementary group of the query "Taken".

I have tried this:

@NamedQuery(name = "Not taken", query = "SELECT b FROM Book b, Bookstaken t WHERE b <> t.bookid")

(and I've tried replacing <> with !=)

But that statement is giving the entire group of Books, every single one of them, and giving that exact group multiple times. Why is this happening? I was never talented at SQL... And how can I fix it?

Thanks

Solution

  • First of all, you have to realize that what you have here is not SQL, but JPQL. These are two different languages.

    Second, your query shows that you have a misconception. The BookTaken entity shouldn't have a bookid field. It should have a toOne association with Book, and thus a field of type Book.

    Finally, the query you have is not correct. What you want is all the books for which there doesn't exist any BookTaken entity referencing this book:

    select b from Book b where not exists (
    select bt.id from BookTaken bt where bt.bookid = b.id)