Problem for selecting queries

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)