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:

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.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|||Here is a solution I have used effectively in the same type of situation as you describe:

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.|||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.|||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|||Sorry, I missed your reply. I haven't tested this, but I'd suggest something like: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|||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.|||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|||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.|||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.|||Temp tables should not be an issue, but you could also use table variables, which exist only within the scope of the procedure.|||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.

Sorry, I missed your reply. I haven't tested this, but I'd suggest something like: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|||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.|||Actually, I think I figured it out... Here is my final query

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)