Click here to Skip to main content
12,691,097 members (32,658 online)

Problem for select ing records from more than one table

devausha asked:

Open original thread
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
Tags: SQL, Server

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).



Advertise | Privacy | Mobile
Web02 | 2.8.170117.1 | Last Updated 26 Mar 2009
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100