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
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.