How to build hierarchy using as few queries as possible (one?)

Question

I have a newer SQLServer with a database with one table of customers, and one table with relationships (parent/child relationships). The relationships may be of different types (ie, standard company hierarchy, friends, competition etc).

I want to list a complete company hierarchy (specific relationship type) by starting with a single customer. Just a complete list, it does not have to be ordered as that will be taken care of outside.

I am able to do this with my web-script which will build the visual part, but there may be some huge hierarchies there and it will cause hundreds of single queries to run. That may become too time-consuming.

I've tried to look at CTE (common table expression), but I don't think I really understand it. I seem to be able to list all where my starting customer is part of, but not where it is not part at all.

Tables:

CREATE TABLE CUSTOMERS
(
 id int not null, 
 name varchar(100) not null
);


CREATE TABLE RELATIONSHIPS
(
 relationid int not null,
 relationtype int not null,
 customerid int not null,
 parentid int not null
);

INSERT INTO CUSTOMERS(id,name) VALUES 
(1040,'Cust A'),
(1041,'Cust B'),
(1042,'Cust C'),
(1043,'Cust D'),
(1044,'Cust E'),
(1045,'Cust F'),
(1046,'Cust G'),
(1047,'Cust H');

INSERT INTO RELATIONSHIPS(relationid,relationtype,customerid,parentid)
VALUES
(1,1,1041,1040),
(2,1,1042,1040),
(3,1,1043,1042),
(4,1,1047,1043),
(5,2,1041,1040);

Current SQL

with cte as (
    select t.parentid,parent.name as parentname,t.customerid,child.name as childname
    from RELATIONSHIPS t
    INNER JOIN CUSTOMERS parent ON t.parentid = parent.id
    INNER JOIN CUSTOMERS child ON t.customerid = child.id
    where relationtype = 1 and (customerid = 1042 or parentid = 1042)

    union all

    select t.parentid,parent.name as parentname,t.customerid,child.name as childname
    from RELATIONSHIPS t
    INNER JOIN CUSTOMERS parent ON t.parentid = parent.id
    INNER JOIN CUSTOMERS child ON t.customerid = child.id
    inner join cte c on (c.customerid=t.parentid )
    where relationtype = 1
)
select distinct t.* from cte t;

Fiddle: http://sqlfiddle.com/#!18/3e919b/14

Based on the data in the data above, i want this listed:

1040,Cust A,1041,Cust B
1040,Cust A,1042,Cust C
1042,Cust C,1041,Cust D
1043,Cust D,1047,Cust H

My query above try to list the entire hierarchy tree where customer 1042 ( Cust C) is part. With my query I seem to get all, except the one for child 1041 (Cust B). Not sure how to include such things in a query, as it isn't directly related to my starting company (except being in same hierarchy).

Answer 1

God this is ugly. No idea if this is the best performer or not. What you're effectively saying is you want all the children of your customer ('Cust C') all the parents of your customer, and then all the children of those parents (that weren't in the prior branch(es)). I've ended up doing this with 3 rCTEs, which is probably an absolute performance killer on a larger data set, but hey, it gets the job "done":

USE Sandbox;
GO

CREATE TABLE CUSTOMERS
(
 id int not null, 
 name varchar(100) not null
);


CREATE TABLE RELATIONSHIPS
(
 relationid int not null,
 relationtype int not null,
 customerid int not null,
 parentid int not null
);

INSERT INTO CUSTOMERS(id,name) VALUES 
(1040,'Cust A'),
(1041,'Cust B'),
(1042,'Cust C'),
(1043,'Cust D'),
(1044,'Cust E'),
(1045,'Cust F'),
(1046,'Cust G'),
(1047,'Cust H');

INSERT INTO RELATIONSHIPS(relationid,relationtype,customerid,parentid)
VALUES
(1,1,1041,1040),
(2,1,1042,1040),
(3,1,1043,1042),
(4,1,1047,1043),
(5,2,1041,1040);
GO

DECLARE @Customer varchar(100) = 'Cust C';
--Get the children of the Customer
WITH Children AS(
    SELECT Cp.name AS ParentName,
           Cp.Id AS ParentID,
           CC.name AS ChildName,
           Cc.id AS ChildID
    FROM CUSTOMERS Cp
         JOIN RELATIONSHIPS R ON Cp.id = R.parentid
         JOIN CUSTOMERS Cc ON R.customerid = Cc.id
    WHERE Cp.name = @Customer
    UNION ALL
    SELECT C.ChildName AS ParentName,
           C.ChildID AS ParentID,
           Cc.name AS ChildName,
           Cc.Id AS ChildId
    FROM Children C
         JOIN RELATIONSHIPS R ON C.ChildID = r.parentid
         JOIN CUSTOMERS Cc ON R.customerid = Cc.id),
--Get the Parents of the customer
Parents AS(
    SELECT Cp.name AS ParentName,
           Cp.Id AS ParentID,
           CC.name AS ChildName,
           Cc.id AS ChildID
    FROM CUSTOMERS Cc
         JOIN RELATIONSHIPS R ON Cc.id = R.customerid
         JOIN CUSTOMERS Cp ON R.parentid = Cp.id
    WHERE Cc.name = @Customer
    UNION ALL
    SELECT Cp.name AS ParentName,
           Cp.Id AS ParentID,
           P.ParentName AS ChildName,
           P.ParentID AS ChildId
    FROM Parents P
         JOIN RELATIONSHIPS R ON P.ParentID = R.customerid
         JOIN CUSTOMERS Cp ON R.parentid = Cp.id),
--Get the children of the parents. Yuck
ParentChildren AS(
    SELECT DISTINCT
           P.ParentName AS ParentName,
           P.ParentID AS ParentID,
           Cc.name AS ChildName,
           Cc.id AS ChildId
    FROM Parents P
         JOIN RELATIONSHIPS R ON P.ParentID = R.parentid
         JOIN CUSTOMERS Cc ON R.customerid = Cc.id    
    WHERE NOT EXISTS (SELECT 1
                      FROM Parents E
                      WHERE E.ChildID = Cc.id
                        AND E.ParentID = P.ParentID)
    UNION ALL    
    SELECT PC.ChildName AS ParentName,
           PC.ChildId AS ParentID,
           Cc.name AS ChildName,
           Cc.id AS ChildID
    FROM ParentChildren PC
         JOIN RELATIONSHIPS R ON PC.ChildId = R.parentid
         JOIN CUSTOMERS Cc ON R.customerid = Cc.id
    WHERE NOT EXISTS (SELECT 1
                      FROM Parents E
                      WHERE E.ChildID = Cc.id
                        AND E.ParentID = PC.ParentID)
)

SELECT *
FROM Children
UNION ALL
SELECT *
FROM Parents
UNION ALL
SELECT *
FROM ParentChildren
ORDER BY ParentID ASC;

GO

DROP TABLE RELATIONSHIPS;
DROP TABLE CUSTOMERS;