hello, sql newbie here.
I have 3 tables(user, education, career), each have their own catalog.
Say I want to search for a person who lives in a certain city with a
certain education and some prior job, where the category equals some
int.
sql:
SELECT DISTINCT * FROM User
INNER JOIN Education ON User.ID = Education.UserID
INNER JOIN Career ON User.ID = Career.UserID
INNER JOIN Category ON User.ID = Category.UserID
WHERE CONTAINS(User.*,'new york')
AND CONTAINS(Education.*,'programming')
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
This doesn't work.
How can I search for multiple items across several columns and tables?
You might want to try this
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND CONTAINS(Education.*,'programming')
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MB" <mbje@.aats.dk> wrote in message
news:9b3ddf86.0507040156.620273fc@.posting.google.c om...
> hello, sql newbie here.
> I have 3 tables(user, education, career), each have their own catalog.
> Say I want to search for a person who lives in a certain city with a
> certain education and some prior job, where the category equals some
> int.
> sql:
> SELECT DISTINCT * FROM User
> INNER JOIN Education ON User.ID = Education.UserID
> INNER JOIN Career ON User.ID = Career.UserID
> INNER JOIN Category ON User.ID = Category.UserID
> WHERE CONTAINS(User.*,'new york')
> AND CONTAINS(Education.*,'programming')
> AND CONTAINS(Career.*,'assistant')
> AND Category.selectedID = 4
> This doesn't work.
> How can I search for multiple items across several columns and tables?
|||thanks for the reply, I tried it, and it worked, the problem is that I
asked the question wrong. Heres what I really wanted to ask:
It seems like User table allows more than one contains
but education and career tables do not.
if I query like so:
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND CONTAINS(Education.*,'programming')
AND CONTAINS(Education.*,'networking')
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
there are no rows returned. If I omit one of the education table
contains, it works.
*** Sent via Developersdex http://www.codecomments.com ***
|||This might work for you
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND CONTAINS(Education.*,"'programming' or 'networking'")
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
as long as programming and or networking were in the same column, otherwise
try
SELECT DISTINCT * FROM [User]
INNER JOIN Education ON [User].ID = Education.UserID
INNER JOIN Career ON [User].ID = Career.UserID
INNER JOIN Category ON [User].ID = Category.UserID
WHERE CONTAINS([User].*,'new york')
AND (CONTAINS(Education.*,"'programming'") or
CONTAINS(Education.*,"'networking"' ))
AND CONTAINS(Career.*,'assistant')
AND Category.selectedID = 4
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Michael Bjerregaard" <mbje@.aats.dk> wrote in message
news:%235H3duSgFHA.272@.TK2MSFTNGP15.phx.gbl...
> thanks for the reply, I tried it, and it worked, the problem is that I
> asked the question wrong. Heres what I really wanted to ask:
> It seems like User table allows more than one contains
> but education and career tables do not.
> if I query like so:
> SELECT DISTINCT * FROM [User]
> INNER JOIN Education ON [User].ID = Education.UserID
> INNER JOIN Career ON [User].ID = Career.UserID
> INNER JOIN Category ON [User].ID = Category.UserID
> WHERE CONTAINS([User].*,'new york')
> AND CONTAINS(Education.*,'programming')
> AND CONTAINS(Education.*,'networking')
> AND CONTAINS(Career.*,'assistant')
> AND Category.selectedID = 4
> there are no rows returned. If I omit one of the education table
> contains, it works.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
