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