Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2004
    Posts
    10

    Unanswered: Complex Recursive Query

    I have a complex query that I am strugling with in MS SQL Server 2000. I am trying to get all of the websites a user has control of. My schema is as follows:

    A user can control many companies, and a company can be controlled by many users. A company can also control other companies (thus a recursive parent relationship). A company can have many websites, but a website can only belong to one company. Thus the design:

    Code:
    USER TABLE
     User_ID
     User_Name
    
    COMPANY TABLE
     Company_ID
     Company_Name
     Company_ID_Parent (recursive)
    
    USER_COMPANY TABLE
     User_ID
     Company_ID
    
    WEBSITE TABLE
     Website_ID
     Company_ID (Foreign Key)
    That said, how do I get a list of websites that a user is associated with... meaning, all of the websites that belong to a company that either the user controls directly, or a child-company of a company that either the user controls directly (at any depth).

    Getting the websites is actually easy. I need the recursive part figured out.

    Thx in Advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do you have a fixed relationship depth (companies owning other companies), or is there no limit? A fixed depth makes a static query simple, which will perform better and is a lot easier to keep portable. A SQL function would allow you to solve the problem allowing infinite depth, but at the expense of additional complexity and loss of portability.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a solution I have used effectively in the same type of situation as you describe:

    Code:
    Create table #CompanyList(Company_ID Int)
    
    Insert into #CompanyList
    	(Company_ID)
    Select	Company_ID
    from	USER_COMPANY
    where	User_ID = [YourUserID]
    
    While @@ROWCOUNT > 0
    Insert into #CompanyList
    	(Company_ID)
    Select	Distinct
    	COMPANY.Company_ID
    From	COMPANY
    	Inner join #CompanyList on COMPANY.Company_ID_Parent = #CompanyList.Company_ID
    Where not exists
    	(select	*
    	from	#CompanyList
    	where	#CompanyList.Company_ID = COMPANY.Company_ID)
    
    Select	Website_ID
    From	WEBSITE
    	Inner join #CompanyList on WEBSITE.Company_ID = #CompanyList.Company_ID
    You can convert this algorithm to a function if you like, or replace the temporary table with a table variable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Dec 2004
    Posts
    10
    Pat,
    Do you have a fixed relationship depth (companies owning other companies), or is there no limit? A fixed depth makes a static query simple, which will perform better and is a lot easier to keep portable. A SQL function would allow you to solve the problem allowing infinite depth, but at the expense of additional complexity and loss of portability.
    Assuming there is a theoretical limit of 3 or 4, what would the query look like?


    Blindman, thanks! I'll take a look at that and see if I can get it to work.

  5. #5
    Join Date
    Jan 2005
    Posts
    10

    recurse the list of member companies

    I think the trick on your problem is to recurse all member companies. then from there, list the sites.

    here's a sample code to use. I guess you get the idea. Sorry if i didnt verify the syntax. My SQL server is down at a the moment.


    ------------------------------------

    -- *** Returns all member companies
    CREATE FUNCTION f_COMPANY_CHILDREN (@PARENT_ID)
    OUTPUT TABLE
    AS
    SELECT A.COMPANY_ID
    FROM COMPANY_TABLE A, F_COMPANY_CHILDREN(A.COMPANY_ID) B
    WHERE A.COMPANY_ID_PARENT = @PARENT_ID
    GO


    -- *** List companies user has access
    SELECT B.USER_ID, A.WEBSITE_ID
    FROM WEBSITE A, USER_COMPANY B
    WHERE A.COMPANY_ID = B.COMPANY_ID
    AND A.COMPANY_ID IN (SELECT COMPANY_ID FROM F_COMPANY_CHILDREN(B.COMPANY_ID))
    UNION
    SELECT B.USER_ID, A.WEBSITE_ID
    FROM WEBSITE A, USER_COMPANY B
    WHERE A.COMPANY_ID = B.COMPANY_ID
    GO

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I missed your reply. I haven't tested this, but I'd suggest something like:
    Code:
    SELECT
       FROM user_table AS u
       INNER JOIN User_company_table AS uc
          ON (uc.User_ID = u.User_ID)
       INNER JOIN (SELECT DISTINCT z4.Company_ID, z1.Company_ID AS zz
          FROM user_company_table AS z1
          INNER JOIN user_company_table AS z2
             ON (z1.Company_ID IN (z2.Company_ID, z2.Company_ID_Parent))
          INNER JOIN user_company_table AS z3
             ON (z2.Company_ID IN (z3.Company_ID, z3.Company_ID_Parent))
          INNER JOIN user_company_table AS z4
             ON (z3.Company_ID IN (z4.Company_ID, z4.Company_ID_Parent)) ) AS c
          ON (c.zz = uc.Company_ID)
       INNER JOIN website_table AS w
          ON (w.Company_ID = c.Company_ID)
    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    WhileTSQL allows true recursive code, I have found that they are much less efficient than the "Accumulator table" algorithm I gave earlier, and I believe that recursion is limited to something like 36 levels. It was this ceiling on an EDI database application that led me to switch to the accumulator method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    WhileTSQL allows true recursive code, I have found that they are much less efficient than the "Accumulator table" algorithm I gave earlier, and I believe that recursion is limited to something like 36 levels. It was this ceiling on an EDI database application that led me to switch to the accumulator method.
    While your solution is more general (it can handle unlimited depth), mine is definitely more portable, and I'd think that mine would perform better too. Each has its benefits, so at least in my mind there isn't a clear cut decision on which one to use.

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For a fixed-level of depth, yours is definitely preferable. Simpler to read, easier to code, and probably more efficient as well. I just prefer using an Accumulator table over a recursive algorithm (such as manilaguy's example) for N-level datasets.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Dec 2004
    Posts
    10
    Thanks for all the info. I think for now I have a theoretical limit, so I will go with Pat's suggestion for testing. But as I build the product, I may need to transition to blindman's way of doing it. For some reason I am just hesitant to create temp tables while many concurrent requests are being handled.

    Thanks again.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Temp tables should not be an issue, but you could also use table variables, which exist only within the scope of the procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Dec 2004
    Posts
    10
    Pat, thanks for the query. I don't see where this links into the Company_Table though. Am I missing something? Perhaps I do not understand the query.

    Quote Originally Posted by Pat Phelan
    Sorry, I missed your reply. I haven't tested this, but I'd suggest something like:
    Code:
    SELECT
       FROM user_table AS u
       INNER JOIN User_company_table AS uc
          ON (uc.User_ID = u.User_ID)
       INNER JOIN (SELECT DISTINCT z4.Company_ID, z1.Company_ID AS zz
          FROM user_company_table AS z1
          INNER JOIN user_company_table AS z2
             ON (z1.Company_ID IN (z2.Company_ID, z2.Company_ID_Parent))
          INNER JOIN user_company_table AS z3
             ON (z2.Company_ID IN (z3.Company_ID, z3.Company_ID_Parent))
          INNER JOIN user_company_table AS z4
             ON (z3.Company_ID IN (z4.Company_ID, z4.Company_ID_Parent)) ) AS c
          ON (c.zz = uc.Company_ID)
       INNER JOIN website_table AS w
          ON (w.Company_ID = c.Company_ID)
    -PatP

  13. #13
    Join Date
    Dec 2004
    Posts
    10
    I tried running an updated version of Pat's query... but I think I'm missing two things.

    1) a join on the company_table (as company_parent_id is not found)
    2) a a where clause to filter for a particular user_ID.

    Any help is appreciated.

  14. #14
    Join Date
    Dec 2004
    Posts
    10
    Actually, I think I figured it out... Here is my final query


    Code:
    SELECT DISTINCT w.WebSite_ID
      FROM tbl_User u INNER JOIN
           tbl_User_Company uc ON uc.User_ID_lkp = u.User_ID 
           INNER JOIN (SELECT DISTINCT z4.Company_ID, z1.Company_ID_lkp AS zz
                       FROM tbl_User_Company z1 INNER JOIN
                       tbl_Company z2 ON z1.Company_ID_lkp IN (z2.Company_ID, z2.Company_ID_prnt) INNER JOIN
                       tbl_Company z3 ON z2.Company_ID IN (z3.Company_ID, z3.Company_ID_prnt) INNER JOIN
                       tbl_Company z4 ON z3.Company_ID IN (z4.Company_ID, z4.Company_ID_prnt)) c 
    ON c.zz = uc.Company_ID_lkp INNER JOIN
    tbl_WebSite w ON w.Company_ID_lkp = c.Company_ID
    WHERE     (u.User_ID = X)

Posting Permissions

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