Join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Join problem

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2002-03-07 : 05:51:53

I have a stored procedure which returns a topic and it's replies. But I have a problem with it. If there aren't any replies for it, the topic itself aren't displayed either. I know the 'inner join' is the reason but nothing I tried worked either. I tried 'left join','right join','left outer join','right outer join' but none of them worked. I can't understand, 'left outer join' should work but I doesn't, still returns empty recordset (in QA). Can you take a look at it please? Here's the sp:

create procedure proc_GetTopic
@topicID int,@LOWER_LIMIT int=0,@UPPER_LIMIT int=25
as
set nocount on

declare @sql nvarchar(3000)

set @sql='select 
	dbo.[TOPICS].T_Forum as T_Forum, 
	dbo.[TOPICS].T_ID as T_ID, 
	dbo.[TOPICS].T_Title as T_Title, 
	dbo.[TOPICS].T_Date as T_Date, 
	dbo.[TOPICS].T_Message as T_Message, 

	dbo.[MESSAGES].M_ID as M_ID, 
	dbo.[MESSAGES].M_TopicID as M_TopicID, 
	dbo.[MESSAGES].M_Time as M_Time, 
	dbo.[MESSAGES].M_Message as M_Message,
from dbo.[MESSAGES] 
inner join dbo.[TOPICS] 
on dbo.[MESSAGES].M_TopicID = dbo.[TOPICS].T_ID
where M_ID not in
(select top '+convert(varchar, @LOWER_LIMIT)+' M_ID 
	from dbo.[MESSAGES] 
	where M_TopicID='+convert(varchar, @topicID)+' 
	order by M_ID desc)
and M_ID in
(select top '+convert(varchar, @UPPER_LIMIT)+' M_ID 
	from dbo.[MESSAGES] 
	where M_TopicID='+convert(varchar, @topicID)+' 
	order by M_ID desc)
and
dbo.[TOPICS].T_ID='+convert(varchar,@topicID)+' 
order by M_Date desc'

exec dbo.sp_executesql @sql

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-07 : 07:24:40

Yes, left join should work, but you need to change the third line from the bottom:
dbo.[TOPICS].T_ID
should be
dbo.[MESSAGES].M_TopicID

kensai
Posting Yak Master

172 Posts

Posted - 2002-03-07 : 11:10:54

It didn't. I tried what you told Arnold but didn't helped any. If the topic doesn't have any replies, the code still returns empty recordset. I don't get it.

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-07 : 11:25:16

If my memory serves me correctly...

To return records from table A that have no corresponding records in table B you have to have something like the following:

select a.id, b.id
from table1 a left outer join table2 b on a.id = b.id
where b.id is null

Because you don't have records in table2 (b) it won't return records on the join critera unless you state that they should be null...I think. It worked for me on a query I had so perhaps it will for you as well.

I hope it helps.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-07 : 12:05:51

I read the question the wrong way round!
Disregard my previous post
RIGHT JOIN and move the NOT IN conditions into the ON.

Edited by - Arnold Fribble on 03/07/2002 15:18:30

kensai
Posting Yak Master

172 Posts

Posted - 2002-03-07 : 17:09:17

quote:

I read the question the wrong way round!
Disregard my previous post
RIGHT JOIN and move the NOT IN conditions into the ON.

Edited by - Arnold Fribble on 03/07/2002 15:18:30

Arnold I'm sorry but I don't think I understand what you mean. Can you explain it a bit please?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-08 : 05:30:33

I meant like this:


from dbo.[MESSAGES] 
right outer join dbo.[TOPICS] 
on dbo.[MESSAGES].M_TopicID = dbo.[TOPICS].T_ID
and M_ID not in
(select top '+convert(varchar, @LOWER_LIMIT)+' M_ID 
        from dbo.[MESSAGES] 
        where M_TopicID='+convert(varchar, @topicID)+' 
        order by M_ID desc)
and M_ID in
(select top '+convert(varchar, @UPPER_LIMIT)+' M_ID 
        from dbo.[MESSAGES] 
        where M_TopicID='+convert(varchar, @topicID)+' 
        order by M_ID desc)
where dbo.[TOPICS].T_ID='+convert(varchar, @topicID)

so that the INs don't affect the topics where there are no messags.

Edited by - Arnold Fribble on 03/08/2002 05:32:08

kensai
Posting Yak Master

172 Posts

Posted - 2002-03-09 : 16:35:18

I'm most grateful Arnold. Couldn't done it myself, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources