Problem for select ing records from more than one table

See more:

SQL

server

I am doing library management. I have three tables which is for Issue books to student and renewal or return books
Issue has two table which is following

<pre lang="SQL">
tblissue

Issueid Code    Issuedate               Student staff   Remarks
1	ISC00	2010-09-17 00:00:00.000	18	0	ad
2	ISC02	2011-09-23 00:00:00.000	15	0	
8	ISC08	2010-09-27 00:00:00.000	0	50	A
34	ISC034	2010-09-27 00:00:00.000	0	4	ad
35	ISC034	2010-09-27 00:00:00.000	0	4	ad
36	ISC034	2010-09-27 00:00:00.000	0	4	ad


tblissuedetails
detid   Issueid Category bookid Qty   Returnorrenewaldate       Reserveid descr
119	65	1	1	 3	2010-09-20 00:00:00.000	NULL	ad
120	78	4	4	1	2010-09-22 00:00:00.000	NULL	
121	78	9	4	1	2010-10-16 00:00:00.000	NULL	gg
122	79	1	1	3	2010-10-30 00:00:00.000	NULL	hhhghh
123	80	5	1	6	2010-10-02 00:00:00.000	NULL	NULL
124	81	9	3	4	2010-09-20 00:00:00.000	NULL	NULL
125	82	3	3	3	2010-10-10 00:00:00.000	NULL	NULL

tblrenewal

ReID ReCode ReDate StaffID StudentID  IssueDetID BookID IsFurtherRenewalAllowed   ReturrnDate   Remarks CategoryID    NumberOfTimeRenewed IsReturned

These are the columns of the Renewal table.
First of all when the students or staff get the books from the library their details or save itinto the tblissue and tblissuedetails. And then when the book is returned or renewal that datas are stored in tblrenewal table.

I want get the following column from that three tables for particular studentid or staffid. When i give the student or staffid it gets the list of all the books which is not returned and no of renewal from corresponding books if the extended date is greater than todays date display the fine amount. If the book is not renewal it gets the details from issue table only or it gets the details from issue and renewal table.
that is look like following

issueid issuecode renewalid renewalcode noofrenewals Extendeddate bookname fine 

If the book is not renewal renewalid and renewalcode is not must noofrenewal will be zero extended date is calculating add 12 from issuedate.

If the book is already renewal it it displays the last renewalid,code, Extendeddate is calculating from last renewaldate which is added to 12 and noofrenewals already made, if it is not renewal that particular date they will be collecting fine, that is one day fine amt is 50. So we calculating howmany days*50

How to get the details help me please?

I have try this

begin 

Declare @count as int
Declare @temptable1 as table
(
IssueID		nvarchar(20),
IssueDetailsID nvarchar(20),
Issuecode	nvarchar(20),
--RenewalId	nvarchar(15),
Titlename	nvarchar(max),
TitleID		nvarchar(20),
IssueDate	datetime,
Retdate		datetime
)


Declare @temptable2 as table
(
IssueDetailsID	bigint,
RenewalID	nvarchar(20),
Renewalcode	nvarchar(20),
RenewalDate datetime,
Titelname	nvarchar(max),
TitleID		nvarchar(20),
Isallowed	bit,
ReturnDate	datetime,
IsReturned	bit,
NooftimeRenewal int
)

Declare @finaltable as table
(
IssueId	bigint,
Issuedate	datetime,
RenewalID	bigint,
Renewaldate	datetime,
Titlename	nvarchar(max),
TitleID		nvarchar(20),
Isallowed	bit,
Returndate	datetime,
IsReturned	bit,
NooftimeRenewal int
)

Begin
insert into @temptable1
Select T1.IssueID,T2.IssueDetailsID,T1.IssueCode, T4.TitleName,T4.TitleID,T1.IssueDate,T2.ReturnorRenewalDate
from tblLibIssue T1
Left outer join tblLibIssueDetails T2 on T2.IssueID=T1.IssueID
Left outer join tblLibStock T3  on T3.StockID=T2.StockID
Left outer join tblLibTitle T4  on T4.TitleID=T2.StockId
where  T1.StudentProfileID=18

Insert into @temptable2
select T1.IssueDetID,T1.RenewalID,T1.RenewalCode,T1.RenewalDate,T2.Titlename,T2.TitleID,T1.IsFurtherRenewalAllowed,T1.Returndate,
T1.IsReturned,T1.NumberofTimeRenewed
from tbllibrenewal T1
Left outer join tblLibStock T3 on T3.StockID=T1.StockId
Left outer join tblLibTitle T2 on T2.TitleID=T3.StockID
Where T1.StudentProfileID=18
End

Select * from @temptable1
Select * from @temptable2

Select t1.issueid,t1.issuedetailsid,t1.titlename,t1.titleid,t1.issuedate,t1.retdate,
t2.renewalid,t2.renewaldate,(Select renewaldate from @temptable2 where issuedetailsid having max(issueddetailsid) group by t1.issueid),(select count(*) from @temptable2  group by t1.issuedetailsid)
from @temptable1 t1
left outer join @temptable2 t2 on t1.issuedetailsid=t2.issuedetailsid
end

but error occured select renewal date which is recently renewaled