Query Help/Ideas - 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 2005 Forums

Transact-SQL (2005)

Query Help/Ideas

Author

Topic

TallOne
Starting Member

49 Posts

Posted - 2008-08-13 : 09:07:56

How could I make the following happen...

I have a two three column tables
TABLE1
ID, Keywords, FAQID

TABLE2
FAQID Question Answer

I need to select a question where ALL of the keywords for that FAQID are in a param description. I thought about dynamic SQL where I could loop through all the items in table1 and create a where clause but there's prolly a better way... Anyone have any ideas?

Here's the query where only one keyword is required....

SELECT DISTINCT
FAQID,Question
FROM
dbo.Table2 t2 INNER JOIN
dbo.Table1 t1 ON t1.FAQID = t2.FAQID
WHERE
@Description Like '%' + t2.KeyWord + '%'

How do I write the query where all keywords must be in the description???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:14:28

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

TallOne
Starting Member

49 Posts

Posted - 2008-08-13 : 09:32:25

CREATE TABLE TABLE1(Keywords VARCHAR(200),FAQID INT)
CREATE TABLE TABLE2(FAQID INT,Question VARCHAR(MAX),Answer VARCHAR(MAX))
INSERT INTO TABLE2(FAQID,Question,Answer)VALUES(1,'This is stupid question','This is a stupid answer')
INSERT INTO TABLE1(Keywords,FAQID)VALUES('help',1)
INSERT INTO TABLE1(Keywords,FAQID)VALUES('resolve',1)
INSERT INTO TABLE1(Keywords,FAQID)VALUES('issue',1)
INSERT INTO TABLE1(Keywords,FAQID)VALUES('Red Herring',2)
DECLARE @Description AS VARCHAR(200)
SET @Description = 'How can we help resolve your issue'
DECLARE @Description2 AS VARCHAR(200)
SET @Description2 = 'How can we help resolve your problem'

The query should return a record for @Description but not for @Description2 because it does not contain all of the keywords...Anyone?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:40:12

[code]-- 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
[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:41:40

[code]SELECT t1.FACID,t1.Question,t1.Answer
FROM TABLE2 t1
CROSS APPLY (SELECT Keywords + '%' AS [text()]
FROM TABLE1
WHERE FAQID=t1.FAQID
FOR XML PATH(''))kl(keylist)
WHERE @Description LIKE '%'+kl.keylist[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:51:27

Dangerous approach Visakh16, even if it is an inventive one.
Your method relies on that the PATH constructor builds the keywords in a string that exactly matches the user supplied sentence.

Change order of inserts for sample data and try again and you will see the flaw in your design.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:54:45

quote:
Originally posted by Peso

Dangerous approach Visakh16, even if it is an inventive one.
Your method relies on that the PATH constructor builds the keywords in a string that exactly matches the user supplied sentence.

Change order of inserts for sample data and try again.

E 12°55'05.25"
N 56°04'39.16"

ah...ok..i understood...yup i need to look at individual values rather than bring them onto string. thanks for pointing out

TallOne
Starting Member

49 Posts

Posted - 2008-08-13 : 10:31:12

Thanks Peso! I was able to work that into my scenario and it worked great! THANKS AGAIN!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources