Need help with SQL query. Keyword matching

[Trenchy]

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(Keywords.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

[philh]

Hi David,

You'll need to join a distinct list of the available keywords to the query:

select count(k.keyword) as kwcount, ws_id,ws_name, k.wgk_keyword
from workgroups,
JOIN workgroups_keywords wk on wgk_wg_id = wg_id
join (select distinct wgk_keyword from workgoups_keywords) k on k.wgk_keyword
= wk.wgk_keyword
where wgk_keyword IN(#QuotedValueList(Keywords.wgk_keyword)#)
GROUP BY ws_id, ws_name
order by wgk_keywor, kwcount DESC

You may have to tweak this to get exactly what you want, but that's the
ballpark idea.

HTH,

[Trenchy]

This worked great:

select distinct(wg_id), count(wg_id) as relevancy, wg_name
from workgroups, workgroups_keywords
where wgk_keyword in (#QuotedValueList(Keywords.wgk_keyword)#)
and
wg_id = wgk_wg_id
and
wg_id <> #wg_id#
group by wg_id, wg_name
order by relevancy desc

Thanks!