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],
[StockID] [int],
[Qty] [int],
[ReturnOrRenewalDate] [datetime],
[ReservedID] [int],
[Description] [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(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)