|
|
Posted by Jason Lepack on 04/16/07 12:41
Carl,
It would take you more time than it's worth to develop a procedure to
search for keywords on the fly in that mess. And once you do create
it, it's going to be really slow because it's not going to be able to
use any indexes. Let's look at a redesign.
Looks to me like you have a many to many relationship between Keywords
and Articles.
tbl_keywords:
keyword_id (unique-idx)
keyword_name (pk)
tbl_article:
article_id (unique-idx)
txtBody
vcrType
tbl_art_key:
keyword_id (Clust-PK)
article_id (Clust-PK)
Now, for your query above with the new design:
SELECT tk.keyword_name, count(tak.article_id)
FROM tbl_keyword tk JOIN tbl_art_key tak
ON tk.keyword_id = tak.keyword_id
Cheers,
Jason Lepack
On Apr 16, 5:15 am, "news.demon.co.uk" <c...@ocvision.com> wrote:
> Hi All,
>
> let me try to explain what I'm trying to accomplish - I really hope someone
> can help.
>
> I have a table (tblArticles) which has the following:
>
> vcrKeywords varchar(2000)
> txtBody text(8000)
> vcrType varchar(128)
>
> and this is a sample of the data
>
> vcrKeyWords || txtBody || vcrType
> key1,key2,key4,key7,key9 || <snipped body> || Site5 News
> key1,key3,key6,key8,key9 || <snipped body> || Site5 News
> key1,key3,key4,key5,key9 || <snipped body> || Site5 News
> key1,key2,key5,key7,key8 || <snipped body> || Site5 News
>
> What I'm trying to accomplish is to return a keyword count based on the
> content of vcrKeywords (i.e. each comma seperated entry as a count.
>
> My SQL statement originally was :
>
> SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticles
> where vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESC
>
> However, this simply matches the entire vcrKeyword Column and not each comma
> seperated value.
>
> Is there a way in SQL that I can achieve this or do I need to use some kind
> of scripting language to accomplish it...?
>
> Regards,
>
> Carl.
Navigation:
[Reply to this message]
|