sql查询从两个表中过滤数据

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)

Hope this will help you.

SELECT sm.receipt_no FROM scheme_master as sm, book_return as br
INNER JOIN book_issue as bi ON sm.book_no2 = bi.book_no
WHERE sm.receipt_no != br.used_receipt

I think this is what you need...

SELECT        scheme_master.Book_no2, scheme_master.receipt_no 
FROM            book_return RIGHT OUTER JOIN
                         Book_issue ON book_return.book = Book_issue.Book_no RIGHT OUTER JOIN
                         scheme_master ON Book_issue.Book_no = scheme_master.Book_no2
WHERE        (Book_issue.Book_no IS NOT NULL) AND  scheme_master.receipt_no != book_return.used_receipt

For the second option getting all records even if no book related record...

SELECT        scheme_master.Book_no2, scheme_master.receipt_no
FROM            scheme_master LEFT OUTER JOIN
                         Book_issue ON Book_issue.Book_no = scheme_master.Book_no2 LEFT OUTER JOIN
                         book_return ON book_return.book = Book_issue.Book_no
WHERE        (Book_issue.Book_no IS NOT NULL AND scheme_master.receipt_no <> book_return.used_receipt) OR
                         (book_return.used_receipt IS NULL)

And finally - similar to your example - but doesn't take more than a jiffy to run:

SELECT        DISTINCT sm.Book_no2, sm.receipt_no
FROM            scheme_master sm LEFT OUTER JOIN
                         Book_issue bi ON bi.Book_no = sm.Book_no2 LEFT OUTER JOIN
                         book_return br ON br.book = sm.Book_no2
WHERE         sm.receipt_no NOT IN (SELECT DISTINCT used_receipt FROM book_return)