选择查询的问题 [英] Problem for selecting queries
本文介绍了选择查询的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有三个表用于图书馆管理系统
tbllibissue
IssueID IssueCode IssueDate StaffProfileID StudentProfileID 备注
56 ISC056 2013-02-07 00:00:00 。 000 0 111 re
57 ISC057 2013-02-12 00:00:00 。 000 0 57 asdfsfdsfsd
55 ISC054 2013-02-06 00:00:00 。 000 0 42 xvxvc
tbllibissuedetails
IssueDetailsID IssueID StockID 数量 ReturnOrRenewalDate Res ervedID 描述
191 57 6 1 2013-02-18 00:00:00 。 000 0 asdfdasf
192 57 4 1 2013-02-18 00:00:00 。 000 0 sdf
193 57 3 1 2013-02-18 00:00:00 。 000 0 asdfsdf
tbllibrenewal
RenewalID RenewalCode RenewalDate StudentProfileID IssueDetID StockID IsReturned
98 RCO98 2013-02-12 57 191 6 1
99 RC099 2013-02-12 57 192 4 0
在第一个和第二个表中用于在向学生发放书籍时插入记录。第三个表用于在相应的图书返回或续订时插入记录。如果书籍退回，''返回''列将为1，否则续订为0.
我想要上面三张表中的记录不是回 。例如
我想通过studentprofileid获得记录= 57
Issuedetailsid stockid
192 4
193 3
如何获得这个。请帮我解决这个问题
解决方案
我一定很伤心，但今天在这里去......
我已经将你的数据表创建为临时表（最佳猜测），并用你的样本数据填充它们......
这里是来自_Maxxx_的解决方案1对那些测试表...
从#IssueDetail选择*
加入#Issue on＃IssueDetail.IssueID =＃Issue.IssueID
不存在的地方（从#Renewal中选择1，其中#Renewal.IssueDetID =＃IssueDetail.IssueDetailsID）
产生此输出（抱歉格式化）
IssueDetailsID IssueID StockID Qty ReturnOrRenewalDate ReservedID描述IssueID IssueCode IssueDate StaffProfileID StudentProfileID备注
193 57 3 1 2013-02-18 0 asdfsdf 57 ISC057 2013-02-12 0 57 asdfsfdsfsd
这里是来自Maciej Los的解决方案2
SELECT RenewalID，RenewalCode，R enewalDate，StudentProfileID，IssueDetID，StockID，IsReturned
FROM #Renewal
WHERE IsReturned = 0
随着它的输出
RenewalID RenewalCode RenewalDate StudentProfileID IssueDetID StockID IsReturned
99 RC099 2013-02-12 57 192 4 0
来自Karthik的解决方案3 Harve ...
SELECT A.IssueID，B.IssueDetailsID，B.StockID FROM #Issue A
INNER JOIN #IssueDetail B ON A.IssueID = B. IssueID
INNER JOIN #Renewal C ON C.IssueDetID = B.IssueDetailsID
WHERE A.StudentProfileID = 57 AND C.IsReturned = 0
输出
IssueID IssueDetailsID StockID
57 192 4
来自Hrushikesh_phapale的解决方案4
选择r。*，s。*，t。*来自
#Renewal r
左连接#IssueDetail s on r.StockID = s.StockID
inner join #Issue t on t.IssueID = s.IssueID
其中r.IsReturned = 0
输出
RenwalID RenewalCode RenewalDate StudentProfileID IssueDetID StockID IsReturned IssueDetailsID IssueID StockID Qty ReturnOrRenewalDate ReservedID描述IssueID IssueCode IssueDate StaffProfileID StudentProfileID备注
99 RC099 2013-02-12 57 192 4 0 192 57 4 1 2013-02-18 0 sdf 57 ISC057 2013-02- 12 0 57 asdfsfdsfsd
最后这里是我试图解释你原来的问题加上你添加到评论中的评论以前的解决方案...
SELECT I.IssueID，I。 IssueCode，I.IssueDate，I.Remarks，
D.IssueDetailsID，D.StockID，D.Qty，D.ReturnOrRenewalDate，D.ReservedID，D。[Description]，
R.IsReturned
FROM #Issue I
INNER JOIN #IssueDetail D on I.IssueID = D.IssueID
LEFT OUTER JOIN #Renewal R on R.IssueDetID = D.IssueDetailsID
WHERE （R.IsReturned = 0 OR R.IsReturned IS NULL ）
AND I.StudentProfileID = 57
随着此输出
IssueID IssueCode IssueDate备注IssueDetailsID StockID数量ReturnOrRenewalDate ReservedID描述IsReturned
57 ISC057 2013-02-12 asdfsfdsfsd 192 4 1 2013-02-18 0 sdf 0
57 ISC057 2013-02-12 asdfsfdsfsd 193 3 1 2013-02-18 0 asdfsdf NULL
所以...选择哪一组输出与你要查找的相匹配，并使用为该输出提供的SQL。
这里有很多不同结果的事实更多地说明你的问题和反应的模糊性，所以下次尝试更明确。
为了完整性，这里是我创建的临时表，以防更多问题跟随...
create table #Issue
（
[IssueID] [ int ]，
[IssueCode] [ varchar ]（ 6 ），
[IssueDate] [ datetime ]，
[StaffProfileID] [ int ]，
[StudentProfileID] [ int ]，
[备注] [ varchar ]（< span class =code-digit> 1000 ）
）
INSERT INTO #Issue VALUES （56 ，' ISC056'，' 2013-02-07'， 0 ，< span class =code-digit> 111 ，' re'）
INSERT INTO #Issue VALUES （< span class =code-digit> 57 ，' ISC057'，' 2013-02-12'， 0 ，< span class =code-digit> 57 ，' asdfsfdsfsd'）
INSERT INTO #Issue VALUES （< span class =code-digit> 55 ，' ISC054'，' 2013-02-06' ， 0 ， 42 ，' xvxvc'）
create table #IssueDetail
（
[IssueDetailsID] [ int ]，
[IssueID] [ int ]， - FK to #Issue
[StockID] [ int ]， - 假设这是本书的FK
[数量] [ int ]，
[ReturnOrRenewalDate] [ datetime ]，
[ReservedID] [ int ]，
[描述] [ VARCHAR ]（ 1000 ） - 避免使用保留字作为列名！
）
INSERT INTO #IssueDetail VALUES （ 191 ， 57 ， 6 ， 1 ，' 2013-02-18'， 0 ，' asdfdasf'）
INSERT INTO #IssueDetail VALUES （ 192 ， 57 ， 4 ， 1 ，' 2013-02-18'，0，' sdf'）
INSERT INTO #IssueDetail VALUES （< span class =code-digit> 193 ， 57 ， 3 ， 1 ，' 2013-02-18'，< span class =code-digit> 0 ，' asdfsdf'）
创建 table #Renewal
（
[RenewalID] [ int ]，
[RenewalCode] [ varchar ]（ 6 ），
[RenewalDate] [ datetime ]，
[StudentProfileID] [ int ]，
[IssueDetID] [ int ]，
[StockID] [ int ]，
[IsReturned] [ int ]
）
INSERT INTO #Renewal VALUES （ 98 ，' RCO98'，' 2013- 02-12'， 57 ， 191 ， 6 ， 1 ）
INSERT INTO #Renewal VALUES （ 99 ，' RC099'，' 2013- 02-12'， 57 ， 192 ， 4 ， 0 ）
选择 *
来自 tbllibissuedetails join tblissue on tblissuedetails.IssueId = tblissue.issueid
其中 不 存在（选择 1 来自 tbllibrenewal 其中 tbllibrenewal.issuedetid = tbllibissuedetails.issueDetailsID）
如果我理解你，下面的查询应该是完美的。
SELECT RenewalID，RenewalCode，RenewalDate，StudentProfileID，IssueDetID，StockID，IsReturned
FROM tbllibrenewal
WHERE IsReturned = 0
I have three tables which is used for library management sysytem
tbllibissue
IssueID IssueCode IssueDate StaffProfileID StudentProfileID Remarks
56 ISC056 2013-02-07 00:00:00.000 0 111 re
57 ISC057 2013-02-12 00:00:00.000 0 57 asdfsfdsfsd
55 ISC054 2013-02-06 00:00:00.000 0 42 xvxvc
tbllibissuedetails
IssueDetailsID IssueID StockID Qty ReturnOrRenewalDate ReservedID Description
191 57 6 1 2013-02-18 00:00:00.000 0 asdfdasf
192 57 4 1 2013-02-18 00:00:00.000 0 sdf
193 57 3 1 2013-02-18 00:00:00.000 0 asdfsdf
tbllibrenewal RenewalID RenewalCode RenewalDate StudentProfileID IssueDetID StockID IsReturned 98 RCO98 2013-02-12 57 191 6 1 99 RC099 2013-02-12 57 192 4 0
In the first and second table is used to insert the records when book is issued to student. And third table is used to insert the records whenever that corresponding book is return or renewal. If the book is return the ''Isreturned'' column will be 1 otherwise it is renewal it is 0.
I want the records from above three tables which is not returned . for example
I want get the records by studentprofileid=57
Issuedetailsid stockid 192 4 193 3
How to get this. Please help me solve this problem
解决方案
I must be really sad today but here goes ...
I''ve created your data tables as temp tables (best guess) and populated them with your sample data ...
Here''s Solution 1 from _Maxxx_ against those test tables ...
select * from #IssueDetail join #Issue on #IssueDetail.IssueID = #Issue.IssueID where not exists (select 1 from #Renewal where #Renewal.IssueDetID = #IssueDetail.IssueDetailsID)
Which produces this output (sorry about the formatting)
IssueDetailsID IssueID StockID Qty ReturnOrRenewalDate ReservedID Description IssueID IssueCode IssueDate StaffProfileID StudentProfileID Remarks 193 57 3 1 2013-02-18 0 asdfsdf 57 ISC057 2013-02-12 0 57 asdfsfdsfsd
Here''s Solution 2 from Maciej Los
SELECT RenewalID, RenewalCode, RenewalDate, StudentProfileID, IssueDetID, StockID, IsReturned FROM #Renewal WHERE IsReturned=0
With it''s output
RenewalID RenewalCode RenewalDate StudentProfileID IssueDetID StockID IsReturned 99 RC099 2013-02-12 57 192 4 0
Solution 3 from Karthik Harve ...
SELECT A.IssueID, B.IssueDetailsID, B.StockID FROM #Issue A INNER JOIN #IssueDetail B ON A.IssueID = B.IssueID INNER JOIN #Renewal C ON C.IssueDetID = B.IssueDetailsID WHERE A.StudentProfileID = 57 AND C.IsReturned = 0
Output
IssueID IssueDetailsID StockID 57 192 4
Solution 4 from Hrushikesh_phapale
select r.*,s.*,t.* from #Renewal r left join #IssueDetail s on r.StockID=s.StockID inner join #Issue t on t.IssueID=s.IssueID where r.IsReturned=0
Output
RenwalID RenewalCode RenewalDate StudentProfileID IssueDetID StockID IsReturned IssueDetailsID IssueID StockID Qty ReturnOrRenewalDate ReservedID Description IssueID IssueCode IssueDate StaffProfileID StudentProfileID Remarks 99 RC099 2013-02-12 57 192 4 0 192 57 4 1 2013-02-18 0 sdf 57 ISC057 2013-02-12 0 57 asdfsfdsfsd
And finally here''s my attempt at interpreting your original question PLUS the comments you''ve added to the previous solutions ...
SELECT I.IssueID, I.IssueCode, I.IssueDate, I.Remarks, D.IssueDetailsID, D.StockID, D.Qty, D.ReturnOrRenewalDate, D.ReservedID, D.[Description], R.IsReturned FROM #Issue I INNER JOIN #IssueDetail D on I.IssueID = D.IssueID LEFT OUTER JOIN #Renewal R on R.IssueDetID=D.IssueDetailsID WHERE (R.IsReturned = 0 OR R.IsReturned IS NULL) AND I.StudentProfileID = 57
With this output
IssueID IssueCode IssueDate Remarks IssueDetailsID StockID Qty ReturnOrRenewalDate ReservedID Description IsReturned 57 ISC057 2013-02-12 asdfsfdsfsd 192 4 1 2013-02-18 0 sdf 0 57 ISC057 2013-02-12 asdfsfdsfsd 193 3 1 2013-02-18 0 asdfsdf NULL
So ... pick which ever set of output matches what you''re looking for and use the SQL provided for that output.
The fact there are so many different results here speaks more to the vagueness of your question and responses so try to be more explicit next time.
For completeness here''s the temp tables I created in case more questions follow...
create table #Issue ( [IssueID] [int], [IssueCode] [varchar](6), [IssueDate] [datetime], [StaffProfileID] [int], [StudentProfileID] [int], [Remarks] [varchar](1000) ) INSERT INTO #Issue VALUES(56,'ISC056','2013-02-07',0,111,'re') INSERT INTO #Issue VALUES(57,'ISC057','2013-02-12',0,57,'asdfsfdsfsd') INSERT INTO #Issue VALUES(55,'ISC054','2013-02-06',0,42,'xvxvc') create table #IssueDetail ( [IssueDetailsID] [int], [IssueID] [int], -- FK to #Issue [StockID] [int], -- Presume this is FK to the book itself [Qty] [int], [ReturnOrRenewalDate] [datetime], [ReservedID] [int], [Description] [varchar](1000) -- Avoid using reserved words as column names! ) INSERT INTO #IssueDetail VALUES(191,57,6,1,'2013-02-18', 0,'asdfdasf') INSERT INTO #IssueDetail VALUES(192,57,4,1,'2013-02-18', 0,'sdf') INSERT INTO #IssueDetail VALUES(193,57,3,1,'2013-02-18', 0,'asdfsdf') create table #Renewal ( [RenewalID] [int], [RenewalCode] [varchar] (6), [RenewalDate] [datetime], [StudentProfileID] [int], [IssueDetID] [int], [StockID] [int], [IsReturned] [int] ) INSERT INTO #Renewal VALUES(98,'RCO98','2013-02-12',57,191,6,1) INSERT INTO #Renewal VALUES(99,'RC099','2013-02-12',57,192,4,0)
select * from tbllibissuedetails join tblissue on tblissuedetails.IssueId = tblissue.issueid where not exists (select 1 from tbllibrenewal where tbllibrenewal.issuedetid = tbllibissuedetails.issueDetailsID)
If i understood you well, below query should works perfect.
SELECT RenewalID, RenewalCode, RenewalDate, StudentProfileID, IssueDetID, StockID, IsReturned FROM tbllibrenewal WHERE IsReturned=0
这篇关于选择查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
