Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Simple FAQ search algorithm

-- Prepare sample data
DECLARE       @Keywords TABLE
       (
              FaqID INT,
              Keyword VARCHAR(200)
       )

INSERT @Keywords
SELECT 1, 'help' UNION ALL
SELECT 1, 'resolve' UNION ALL
SELECT 1, 'issue' UNION ALL
SELECT 2, 'Red Herring'

DECLARE       @Faq TABLE
       (
              FaqID INT,
              Question VARCHAR(MAX),
              Answer VARCHAR(MAX)
       )

INSERT @Faq
SELECT 1, 'This is stupid question', 'This is a stupid answer'

DECLARE @Question AS VARCHAR(200)

-- Prepare user supplied parameter
SET @Question = 'How can we help resolve your issue'

-- Show the expected result
SELECT        f.Question,
              f.Answer
FROM          @Faq AS f
INNER JOIN    (
                     SELECT        FaqID         
                     FROM          @Keywords
                     GROUP BY      FaqID
                     HAVING        SUM(CASE WHEN @Question LIKE '%' + Keyword + '%' THEN 1 ELSE 0 END) = COUNT(*)
              ) AS d ON d.FaqID = f.FaqID

-- Prepare user supplied parameter
SET @Question = 'How can we help resolve your problem'

-- Show the expected result
SELECT        f.Question,
              f.Answer
FROM          @Faq AS f
INNER JOIN    (
                     SELECT        FaqID         
                     FROM          @Keywords
                     GROUP BY      FaqID
                     HAVING        SUM(CASE WHEN @Question LIKE '%' + Keyword + '%' THEN 1 ELSE 0 END) = COUNT(*)
              ) AS d ON d.FaqID = f.FaqID

 

Print | posted on Wednesday, August 13, 2008 4:27 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET