On 1 Nov 2004 08:23:44 -0800, Silvio wrote:

>Does anyone would know a better,smart, more efficient way to
>accomplish this task?

Hi Silvio.

Yep. Improve your table design. You should normalize down to at least
third normal form. That emans that the repeating group (L1 ... L10) in the
Leaf table has to go in it's own table:

CREATE TABLE Paths
(TreeID int NOT NULL REFERENCES Trees,
PathNo int NOT NULL CHECK (PathNo BETWEEN 1 AND 10),
LeafID int NOT NULL REFERENCES Leaves,
PRIMARY KEY (TreeID, PathNo)
)

If the same leaf can't occur twice in a tree, you could also add a
UNIQUE(TreeID, LeafID) constraint, or make that the primary key. If the
order of the leaves in a tree is unimportant, you can leave out the PathNo
column.


>I need to lookup for a given keyword in each path of the tree table,
>and return each individual column for the paths that match the
>criteria.

Probably something like this:

SELECT P.PathNo, L.LeafText
FROM Paths AS P
INNER JOIN Leaves AS L
ON L.LeafNo = P.LeafNo
WHERE EXISTS (SELECT *
FROM Paths AS P2
INNER JOIN Leaves AS L2
ON L2.LeafNo = P2.LeafNo
WHERE P2.TreeID = P.TreeID
AND CHARINDEX(@keyword, L2.LeafText) > 0)
(untested)

Best, Hugo
--

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