Mysql request available books for a new reader

Question

I am doing a library management system project. I am the mysql query staff.

I have a table of books (books) in these fields book_id | book_name | isbn_no| publisher_info

I have another reader table (reader) in this reader_id | book_id | reader_name | reader_info | and book_return_status

Now I need the result of a list of books that is not in the reading table, so that the list becomes an accessible list of books.

select * from books,reader where books.book_id != reader.book_id and reader.book_return_status = 1 

But it does not give me the correct result, I also tried this one request.

 select * from books where book_id not in (select * from reader where book_return_status = 1)

But I did not give my result.

I want to order a list that is not in the readers table.

Answer 1

You are close to your second request. However, to work, not inyour sub-request should only return identifiers:

 select * from books where book_id not in (select book_id from reader where book_return_status = 1)

Answer 2

select * from books where book_id not in (select reader_id from reader where book_return_status = 1)