从多个表中选择记录的问题 [英] Problem for select ing records from more than one table
问题描述
我在做图书馆管理。我有三张桌子，用于向学生发放书籍和续费或退还书籍
问题有两张表格，以下是
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
这些是续订表的栏目。
首先，当学生或员工从图书馆获取书籍时，他们的详细信息或将其保存到tblissue和tblissuedetails。然后当书籍被退回或更新时，数据存储在tblrenewal表中。
我想从三个表中获取特定studentid或staffid的以下列。当我给学生或工作人员时，如果延长日期大于今天的日期，则会获得所有未退回的书籍清单以及相应书籍的续订，显示罚款金额。如果图书没有续订，它只会从发行表中获取详细信息，或者从发行和续订表中获取详细信息。
如下所示
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
如果图书不续订续订和续订码不是必须noofrenewal将是零延长日期是计算从发行者添加12。
如果图书已经续订，它会显示最后一次续订，代码，Extendeddate正在计算上次续订日期到12和noofrenewals已经做了，如果它没有更新特定日期他们将收集罚款，这是一天罚款amt是50.所以我们计算howmany天* 50
如何获取详细信息请帮助我？
我试试这个
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
推荐答案
更新此行
Update this line
(select count(1) from @temptable2 t3 where t1.issuedetailsid=t3.issuedetailsid)
这篇关于从多个表中选择记录的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
