exists 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)

exists problem

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2007-07-13 : 04:38:26

can anyone tell me whats up with the sql statement. I only want the values to be inserted into the temp table if they do not exist already. At the mo the values are inserted regardless

insert into
#treeSearch
select
[table].id,
[table].title,
0
from
dbo.Work_section
inner join Category on dbo.Work_section.category_id = Category.id
inner join [table] on dbo.Category.table_id = [table].id
where contains
(Work_section.title,@searchTerm) and
not exists (select id from #treeSearch where id = [table].id)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 05:10:43

Are you using FREETEXT? Not? CONTAINS is a reserved keyword for FREETEXT search.

INSERT		#TreeSearch 
SELECT		t.ID,
		t.Title,
		0
FROM		dbo.Work_Section AS ws
INNER JOIN	Category AS c ON c.ID = ws.Category_ID
INNER JOIN	[Table] AS t ON t.ID = c.Table_ID
LEFT JOIN	#TreeSearch AS s ON s.ID = t.ID
WHERE		s.ID IS NULL
		AND CONTAINS(ws.Title, @SearchTerm)

Peter Larsson
Helsingborg, Sweden

rico_bano
Starting Member

35 Posts

Posted - 2007-07-13 : 05:50:38

thanks for that, but it still doesnt seem to work. s.ID always seems to be null. I really dont understand why. this should work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 07:57:18

Please post some sample data from all tables. Also provide expected output.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources