Table name - scheme_master
column name - book_no2,receipt_no
Table name - book_issue
column name - book_no
Table name - book_return
column name - used_receipt
I need to select only those receipt_no from scheme master whose book is created on book issue table.. and receipt_no which is not present in book_return table.
FOR EX -
DATA in scheme_master
Book_no2 receipt_no
1 - 10
1 - 11
2 - 14
2 - 15
3 - 18
3 - 19
DATE IN Book_issue table
Book_no
3
book used_receipt
3 18
DATA in book_return table
In above example there are 3 books created in scheme master table 1,2,3 with diff. receipt OK.
BUT in Book issue table we allot only book no 3.
AND in Book return table we used only receipt no 18.
So i need to select all only receipt no 19 of book 3 from scheme master table...
-plz help to get this...
Below is my query it works fine ...
But this takes lot of time to select records...
FOR EX -it takes more than 6 minutes to Fetch from 30 records...
SELECT DISTINCT SM.receipt_no
FROM scheme_master SM
Inner join book_issue BI ON BI.book_no = SM.Book_no2
where
SM.receipt_no not in (select used_receipt from book_return)