CODEWITHSUNDEEP
phpmysql
user3044948
user3044948

Reputation: 41

mysql query for available books for new reader

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

I have books table (books), in that fields are book_id | book_name | isbn_no| publisher_info

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

Now I want the result of book list which are not in reader table, so that list will become an available book list. 

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

But not gives me proper result, I have also tried out this one query..

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

But I haven't given my result.

I want to book list which are not in reader table.

Upvotes: 4

Views: 87

Answers (2)

Hanlet Escaño
Hanlet Escaño

Reputation: 17380

You are close with your second query. However, for not in to work, your sub-query must return only ids:

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

Upvotes: 3

Nikul
Nikul

Reputation: 1025

Try this query 

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

Upvotes: 0

Related Questions