contains(co1, col2)

Question

contains(co1, col2)

Tim Abbott

SSC Rookie

Points: 38
More actions
March 7, 2005 at 4:24 pm

#66055
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 cINNER JOIN UserAlerts a ON a.Make=c.Make AND a.Model=c.ModelWHERE CONTAINS(c.CarDescription, a.UsersKeywords)
```
Does anyone know how I can get this to work / what I'm doing wrong / know how of a way to acheive this?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

So does UsersKeywords field hold the information you're looking for in CarDescription? If so, then you'll need to store it as a local variable for this to work.

EG: declare <blah> etc...
SET @keywords = a.UsersKeywords
SELECT c.*
FROM Cars c
INNER JOIN UserAlerts a
ON a.Make=c.Make
AND a.Model=c.Model
WHERE CONTAINS(c.CarDescription, @keywords)

--------------------
Colt 45 - the original point and click interface

Tim Abbott SSC Rookie Points: 38 More actions · Answer 2

Hi Phill.

Thanks for the reply.

When I tried to run the above query i get the following error message:

Server: Msg 107, Level 16, State 2, Line 2

The column prefix 'a' does not match with a table name or alias name used in the query.

David Burrows SSC Guru Points: 65144 More actions · Answer 3

Phill is right you will have to use either a string or a variable you cannot use another column in CONTAINS.

The error is in the first line as not tablename is specified for the alias a. Besides the solution will not work without extracting each UsersKeywords one at a time into a variable and then doing CONTAINS.

You will need to find another way of doing this and that will depend on the structure and contents of CarDescription and UsersKeywords.

Far away is close at hand in the images of elsewhere.
Anon.

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 4

Don't know if this is applicable to this situation (or even if I have completely understodd it) but you might want to try a subquery inside the parens to return the list of values to compare against. If the values are coming from different tables, as the prefixes implies, you might have to use a Union of two query resultsets into one list.

HTH

Don