one for sql guru's

[tim_s]

Hi!

I need to use CONTAINS on two columns as a join condition but I can't get it to work. Here is some code...


SELECT c.*
FROM Cars c
INNER JOIN UserAlerts a ON a.Make=c.Make AND a.Model=c.Model
WHERE CONTAINS(c.CarDescription, a.UsersKeywords)


Sample data for c.CarDescription is "Golf 1.8T Gti 3dr".

Sample data for a.UsersKeywords is "1.8 AND 3dr AND Gti".

Does anyone know how I can get this to work / what I'm doing wrong?

Maybe a stored procedure could do it? I've been looking for days and can't find the answer anywhere.



i have a backup solution but it's a pain to debug so i'm not looking forward to using it...

CREATE VIEW MatchingKeywords AS

SELECT k.AlertId, c.CarId, COUNT(*) AS NumKeywords,
SUM(CASE WHEN CHARINDEX(k.Keyword, c.Description) > 0 THEN 1 ELSE 0 END) AS MatchingKeywords
FROM MCF_Keyword k
INNER JOIN MCF_Alert a ON k.AlertId=a.AlertId
LEFT OUTER JOIN MCF_Car c ON a.Make=c.Make AND a.Model=c.Model
WHERE a.Type='N'
GROUP BY k.AlertId, c.CarId



SELECT c.*
FROM Cars c
INNER JOIN UserAlerts a ON a.Make=c.Make AND a.Model=c.Model
INNER JOIN MatchingKeywords k ON k.AlertId=a.AlertId
WHERE k.NumMatchingKeywords=k.NumKeywords


>>> Edited by tim_s on Monday 7th March 23:33

>>> Edited by tim_s on Monday 7th March 23:33

>>> Edited by tim_s on Monday 7th March 23:33