Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
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
Posted 12-Feb-13 1:41am
devausha1.4K
Edited 12-Feb-13 3:00am
v3
Comments
Rai Pawan at 12-Feb-13 6:50am
   
do you want to show all the books that have been not been returned as on date or only books not returned by a particular user as on date?
- Pawan
devausha at 12-Feb-13 7:10am
   
I want to show all the books not returned by a particular user
CHill60 at 12-Feb-13 7:21am
   
As an aside from the solutions below and for information only ... most developers do not prefix table names with either "tbl" or the name of the database (e.g. "lib" ?). Reason - it's a lot of extra typing that doesn't add any information but does obfuscate the real name of the table. Table names of Issue, IssueDetails and Renewal are clearer.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

select * 
from tbllibissuedetails join tblissue on tblissuedetails.IssueId = tblissue.issueid
where not exists (select 1 from tbllibrenewal where tbllibrenewal.issuedetid = tbllibissuedetails.issueDetailsID)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If i understood you well, below query should works perfect.
SELECT RenewalID, RenewalCode, RenewalDate, StudentProfileID, IssueDetID, StockID, IsReturned
FROM tbllibrenewal
WHERE IsReturned=0
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi, try this query.
 
SELECT A.IssueID, B.IssueDetailsID, B.StockID FROM 
tbllibissue A INNER JOIN tbllibissuedetails B ON A.IssueID = B.IssueID
INNER JOIN tbllibrenewal C ON C.IssueDetID = B.IssueDetailsID
WHERE A.StudentProfileID = 57 AND C.IsReturned = 0
 
hope it helps.
  Permalink  
v2
Comments
CHill60 at 12-Feb-13 7:15am
   
I think you meant WHERE A.StudentProfileID = 57
Karthik Harve at 12-Feb-13 7:17am
   
Yes.
devausha at 12-Feb-13 7:15am
   
this query is displays only issuedetailsid 191
I want to show 192 also
devausha at 12-Feb-13 7:18am
   
No, I try this. But it is not display
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

select r.*,s.*,t.* from
     tbllibrenewal r 
     left join tbllibissuedetails s on r.StockID=s.StockID
     inner join tbllibissue t on t.IssueID=s.IssueID
where r.IsReturned=0
  Permalink  
v2
Comments
devausha at 12-Feb-13 7:14am
   
It is not working I want just show without returned book. If that book is not returned only renewal isreturned is 0.
So both 192,193 issuedetailsid will be shown

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 365
1 Sergey Alexandrovich Kryukov 319
2 CPallini 275
3 DamithSL 214
4 Maciej Los 185
0 OriginalGriff 5,455
1 DamithSL 4,457
2 Maciej Los 3,885
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,115


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2014
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