|
|
Posted by Jason Lepack on 04/16/07 12:44
Ah, the joys of not proofreading...
Now, for your query above with the new design:
SELECT tk.keyword_name, count(tak.article_id) keycount
FROM tbl_keyword tk
JOIN tbl_art_key tak
ON tk.keyword_id = tak.keyword_id
JOIN tbl_article ta
ON ta.article_id = tak.article_id
WHERE ta.vcrType LIKE 'site 5%'
GROUP BY tk.keyword_name
ORDER BY keycount DESC
On Apr 16, 8:41 am, "Jason Lepack" <jlep...@gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Navigation:
[Reply to this message]
|