sql查询连接三个表并选择数据（如果存在）

Question

Below is my three table with data..

I need to select book_no2,receipt_no,createddate from schememaster and join book issue and book return table..

Now need to check, if book is issued then display issuedate against this book.

AND If book is returned then need to be display returneddate against those book and receipt no..

table name schemeMaster

book_no2    receipt_no      createddate

70          100           2015-02-20
70          101           2015-02-20
70          102           2015-02-20
70          103           2015-02-20
70          104           2015-02-20
70          105           2015-02-20
70          106           2015-02-20

Table name bookissue

bookno         issuedate
 70            04-03-2015    
 70            05-03-2015
 70            06-03-2015

Table name bookreturn

bookno   usedreceipt         returndate
 70          100             04-03-2015
 70          101             04-03-2015
 70          102             05-03-2015
 70          103             07-03-2015

Expected Output

  bookno receiptno   createdddate   IssueDate     ReturnDate    
  70      100         2015-02-20    04-03-2015    04-03-2015     
  70      101         2015-02-20    ----------    04-03-2015     
  70      102         2015-02-20    05-03-2015    05-03-2015     
  70      103         2015-02-20    06-03-2015    07-03-2015     
  70      104         2015-02-20    ---------     ---------
  70      105         2015-02-20    ---------     ---------      
  70      106         2015-02-20    ---------     ---------      

I try below query, getting data but not proper as i want..

My query Display issue and returndate to all rows....in those receipt also which are not available in book return table..

FOR EX - In my above ex - there are 3 tables with data ...

I need to select all data from scheme master table and display issuedate and returndate if available else display blank on this column...

SELECT DISTINCT SM.book_no2 As Book, 
SM.created AS Printeddate, 
BI.issue_date AS issuedate, 
BR.return_date AS returndate,
BR.used_receipt AS totalreceipt 
FROM scheme_master SM 
LEFT JOIN book_issue BI 
ON (SM.book_no2 = BI.book_no ) 
LEFT JOIN book_return BR 
ON ( BR.book = BI.book_no ) 
WHERE SM.city = 'nagpur' 
AND SM.book_no2 BETWEEN 70 AND 70

Answer 1

select s.book_no2, s.reciept_no, s.createddate, b.issuedate, br.returndate
from schememaster as s
left join bookissue as b on b.bookno = s.book_no2
left join bookreturn as br on br.bookno = s.book_no2

This will produce results like your output, but not sure if its what you want.