Need help with SQL query. Keyword matching.

Hi all,

I have two tables:

workgroups (wg_id, wg_name)
workgroups_keywords (wgk_wg_id, wgk_keyword)

Each workgroup has an associated list of one or more keywords.

What I want do to at first was given a particular list of keywords
bring back a list of workgroups that have at least one matching keyword
associated with it.

I have the following query:


select distinct(wg_id), wg_name
from workgroups, workgroups_keywords
where
wgk_keyword in  (#QuotedValueList(Ke
ywords.wgk_keyword)#)
and
wg_id = wgk_wg_id
order by wg_name



This works great.

However, is there a way in a single query to order the returned rows by
the number of keywords that are found to be matching (in other words an
order by relevancy, the more keywords that match the more relevant the
returned row)?

Thanks in advance.

David

(david@iaction.com)  writes:
> I have two tables:
>
> workgroups (wg_id, wg_name)
> workgroups_keywords (wgk_wg_id, wgk_keyword)
>
> Each workgroup has an associated list of one or more keywords.
>
> What I want do to at first was given a particular list of keywords
> bring back a list of workgroups that have at least one matching keyword
> associated with it.
>
> I have the following query:
>
>     select distinct(wg_id), wg_name
>     from workgroups, workgroups_keywords
>     where
>     wgk_keyword in  (#QuotedValueList(Ke
ywords.wgk_keyword)#)
>     and
>     wg_id = wgk_wg_id
>     order by wg_name

This syntax looks funny to me. Are you really using Microsoft SQL Server?

> This works great.
>
> However, is there a way in a single query to order the returned rows by
> the number of keywords that are found to be matching (in other words an
> order by relevancy, the more keywords that match the more relevant the
> returned row)?

That sounds doable. It would be something like

SELECT wg_nmae, COUNT(*)
FROM   ...
GROUP  BY wg_name
ORDER  BY 2

But since I don't know exactly how this Keywords.wgk_keyword works,
and I'm uncertain that you are using MS SQL Server, I refrain from a
complete answer.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

On 27 Jul 2005 08:50:27 -0700, david@iaction.com wrote:

>Hi all,
>
>I have two tables:
 (snip)
>Thanks in advance.
>
>David

Hi David,

You posted the same question in microsoft.public.sqlserver.programming,
where it was answered. Please don't post the same question to multiple
groups. Posting to one group is the preferred method. And if you really
feel that the question belongs in two groups, then please usse your
software's crossposting facility to get the same message in both groups,
so that replies in one group will also be visible in the other. That
saves other people from spending time and energy to answer a question
that is already answered elsewhere.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)