Sql Server Cte Left Outer Join

I have 2 tables in SQL Server 2008, customertest with columns customer id (cid) and it's boss id (upid), and conftest with cid, confname, confvalue customertest schema and data: c

Solution 1:

If it's unknown how many levels there are in the hierarchy?

Then such challenge is often done via a Recursive CTE.

Example Snippet:

---- Using table variables for testing reasons--declare@customertesttable (cid intprimary key, upid int);
declare@conftesttable (cid int, confname varchar(6) default'budget', confvalue int);
---- Sample data--insertinto@customertest (cid, upid) values 
(1,0), (2,1), (3,1), (4,2), (5,2), (6,3), 
(7,5), (8,5), (9,8), (10,9);
insertinto@conftest (cid, confvalue) values 
(1,1000), (2,700), (3,300), (4,100), (5,200), (6,300);

-- The customer that has his own budget, or not.declare@customerIDint=10;

;with RCTE AS 
(
  ---- the recursive CTE starts from here. The seed records, as one could call it.--select cup.cid as orig_cid, 0as lvl, cup.cid, cup.upid, budget.confvalue
  from@customertestas cup
  leftjoin@conftest budget on (budget.cid = cup.cid and budget.confname ='budget')
  where cup.cid =@customerID-- This is where we limit on the customerunionall---- This is where the Recursive CTE loops till it finds nothing new--select RCTE.orig_cid, RCTE.lvl+1, cup.cid, cup.upid, budget.confvalue
  from RCTE
  join@customertestas cup on (cup.cid = RCTE.upid)
  outer apply (select b.confvalue from@conftest b where b.cid = cup.cid and b.confname ='budget') as budget
  where RCTE.confvalue isnull-- Loop till a budget is found
)
select 
 orig_cid as cid, 
 confvalue
from RCTE
where confvalue isnotnull;    

Result :

cid confvalue
--- ---------
 10       200

Btw, the Recursive CTE uses the OUTER APPLY because MS SQL Server doesn't allow a LEFT OUTER JOIN to be used there.

And if it's certain that there's maximum 1 level depth for the upid with a budget? Then just simple left joins and a coalesce would do.

For example:

select cup.cid, coalesce(cBudget.confvalue, upBudget.confvalue) as confvalue
from@customertestas cup
leftjoin@conftest cBudget on (cBudget.cid = cup.cid and cBudget.confname ='budget')
leftjoin@conftest upBudget on (upBudget.cid = cup.upid and upBudget.confname ='budget')
where cup.cid =8;

Solution 2:

I don't think you are looking for a CTE to do that, from what I understand:

CREATETABLE CustomerTest(
    CID INT,
    UPID INT
    );
CREATETABLE ConfTest(
    CID INT,
    ConfName VARCHAR(45),
    ConfValue INT
    );

INSERTINTO CustomerTest VALUES
(1, 0),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3),
(7, 5),
(8, 5);

INSERTINTO ConfTest VALUES
(1, 'Budget', 1000),
(2, 'Budget', 700),
(3, 'Budget', 300),
(4, 'Budget', 100),
(5, 'Budget', 200),
(6, 'Budget', 300);

SELECTMAX(CNT.CID) AS CID,
       CNT.ConfName,
       MIN(CNT.ConfValue) AS ConfValue
FROM ConfTest CNT INNERJOIN CustomerTest CMT ON CMT.CID = CNT.CID
                                              OR CMT.UPID = CNT.CID
WHERE  CMT.CID =7-- You can test for values (8, 4) or any value you want :)GROUPBY
       CNT.ConfName;