Hi experts,I need some help with a stored procedure which is used to search articles in the database for words or sequences of words in a phrase. To a degree I'm trying to write my own full text search functionality.I have a table of tags : create table tags (id bigint, tag varchar(25))
and a table which contains every tag in sequence for the articles that users of my program will write : create table articletags (id bigint, articleid bigint, tagid bigint, nextarticletagid bigint)
This is a fairly simple setup which lets me find articleids for articles which contain a given tag or tags. Finding single words isn't a problemMy problem is in the intended use of articletags.nextarticletagid which lets me find phrases within articles because an articletag record is linked to another in sequenceHowever I'm a bit stuck as to how I can perform a search for a phrase, in other words - find separate articletags records that are linked via articletags.nextarticletagid.For example :article
id content--------------100 no red squirrels live heretags
id tag-----------------20 salad21 no22 red23 squirrels24 live25 here26 salmonarticletags
id articleid tagid nextarticletagid tags.tag--------------------------------------------------1 100 21 2 no2 100 22 3 red3 100 23 4 squirrels4 100 24 5 live5 100 25 NULL here
I probably need some kind of recursive code but am not sure how to approach this in sql. Can anyone help?ps: The articletags table also includes weighting data for each tag, depending on whether the tag is in a header, subheader, list or paragraph. The following sql returns articleids in ranked order but only works for single tags, how can I make it work for phrases (sequences of tags)?select articleid, sum(weighting) as rank, dateupdatedfrom articletagsinner join article on article.id = articleidwhere tagid in (100)group by articleid, dateupdatedorder by rank desc, dateupdated desc
Thanks
Rescursive sql to retrieve linked records?
Posted - 2007-02-05 : 15:50:19