Complex Searching - 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

General SQL Server Forums

Database Design and Application Architecture

Complex Searching

Author

Topic

TimmyC
Starting Member

10 Posts

Posted - 2007-11-22 : 12:00:10

Hello all,

I have been given the task of adding an option to "Search Top Stories" on an existing full text search we already had. I thought this would be easy, just another content type to throw in, but regretfully top stories are calculated on this site on the fly it seems.

The original search (made back in 2003 I think) did the full text search with various cases for various options. I don't understand the full relevance of all of them but its not really necessary. I thought an easy solution would be to get the IDs of top stories and run the same search and include only if the ID is in the top stories list (how disgustingly inefficient, but I really don't have time to rebuild it all - especially when I dont know where else this SP is used). I get an error in the query analyser:
Server: Msg 156, Level 15, State 1, Line 87
Incorrect syntax near the keyword 'WHERE'.

For the life of me I can't figure out what its complaining about.

This is the SQL for the search on the top stories only (so 2nd half of the union):

SELECT DISTINCT TOP 100 PERCENT
S.SearchID, S.TypeID, S.IndexID, CT.Rank, S.ArchiveDate,
CASE @Group
WHEN 1 THEN S.TypeID
END,
CASE @OrderType
WHEN 1 THEN ArchiveDate
END as a,
CASE @OrderType
WHEN 2 THEN ArchiveDate
END as b,
CASE @OrderType
WHEN 3 THEN Rank
END as c

FROM TT_Articles A
INNER JOIN TT_XChannelArticle ON A.Article_ID = TT_XChannelArticle.Article_ID
RIGHT OUTER JOIN TT_SearchIndex S
INNER JOIN ContainsTable(TT_SearchIndex,Content,@SearchTerms) CT ON S.SearchID=CT.[Key]
LEFT OUTER JOIN TT_XChannelArticle XA ON S.IndexID=XA.Article_ID AND S.TypeID IN (2,3,4,5) AND XA.Channel_ID IN (SELECT * FROM @ChannelTable)
LEFT OUTER JOIN TT_PressReleases PR ON S.IndexID=PR.Press_ID AND S.TypeID=1 AND PR.Channel_ID IN (SELECT * FROM @ChannelTable)

WHERE A.ArticleID IN (
SELECT DISTINCT TT_Articles.Article_ID
FROM TT_Articles INNER JOIN
TT_XChannelArticle ON TT_Articles.Article_ID = TT_XChannelArticle.Article_ID
WHERE (TT_Articles.ArticleDate <= GETDATE()) AND (TT_Articles.Approved = 1) AND (TT_Articles.Type = 2) AND (TT_XChannelArticle.Channel_ID IN (SELECT * FROM @ContentTable)))

If you would like I can post the original stored procedure.

@ChannelTable and @ContentTable are comma-delimited lists that have been turned into single field tables.

Whoa what a post, thanks for any help you can give guys. I know the design can really be improved, and I'm sure we will do a rebuild next year.

Tim

TimmyC
Starting Member

10 Posts

Posted - 2007-11-22 : 12:03:18

Oh and apologies, but this line is line 87.
WHERE A.ArticleID IN (

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-22 : 15:21:52

You're missing the ON clause for your RIGHT JOIN. It's getting reported as an error near the WHERE just because that's where it's expecting to see the last ON.

TimmyC
Starting Member

10 Posts

Posted - 2007-11-23 : 05:38:03

Ah yes I see, thanks very much, snSQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources