Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Unanswered: create a path in a sql statment

    Hi there,

    I'm a new Web developer and using SQL Server 2012 for my databases.

    Now I'm trying to write a search module. Therefore I have to create a view, with all datas to make it easy for searching.
    Unfortunately I have a table, which creates a tree (let's say, it's a navigation tree). For the view, I like to have the navigation as a path:

    /Main/FirstSub/SecondSub

    My navigation tbl looks like this:

    Code:
    ID	ParentID	Text
    1	0		Main
    2	0		otherMain
    3	0		otherMain
    4	1		FirstSub
    5	3		otherFirstSub
    6	5		otherSecondSub
    7	4		SecondSub
    Now I like to get a SQL statement where I will get the path.
    What I did until now is:

    Code:
    SELECT 'Article' AS 'Table', art.NavID AS 'NavID', art.ID AS 'TextID', art.Text AS 'Text' 
    FROM tArticles AS art
    
    UNION ALL
    
    SELECT 'Title' AS 'Table', tit.NavID AS 'NavID', tit.ArtID AS 'TextID', tit.Text AS 'Text' 
    FROM tTitles AS tit
    
    UNION ALL
    
    SELECT 'Navigation' AS 'Table', nav.ID AS 'NavID', 0 AS 'TextID', nav.Text AS 'Text' 
    FROM tNavigation AS nav
    but nav.Text should be the path. How can I achieve this?
    in a SP I allready tried it with the following code:

    Code:
    DECLARE @intFlag INT, @NavPath NVARCHAR(1000), @NavText NVARCHAR(100)
    SET @intFlag = @NavID
    WHILE (@intFlag > 0)
    BEGIN
    SET @NavText = (SELECT nav.Text FROM vNavigation AS nav WHERE nav.ID = @intFlag)
    SET @intFlag = (SELECT nav.ParentID FROM vNavigation AS nav WHERE nav.ID = @intFlag)
    SET @NavPath = CONCAT('/', @NavText , @NavPath )
    END
    Or is there any other solution I don't know or don't see?

    Thank you for your help.

    Marc

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's the limit of the number of items in the "path"?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, why do you need to create a view like that for searching?

    Surely just searching the "Text" column in your table will provide you the answers you are looking for without the need to "path" them.
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2013
    Posts
    4
    There is no limit set for the items in the path. But the items are only creatable at the sql server (so no dynamic itemlist). at the moment there is a max of 3 items in the list.

    yes, it's right. then I will get the answer. but what happens, if the user like to search for 2 or more words. therefore I need to go through the list with NEAR

  5. #5
    Join Date
    Oct 2013
    Posts
    4
    and if the user looks for "FirstSub" he will not find what he looks for, because I only save the last item of the path and generate the path with its parents.

    you can just search the last part in the path. But if you have the full path you can search every item in it.

    do you know, what I mean?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •