0

With the following SCHEMA

CREATE TABLE Software (
    ID INT,
    name VARCHAR(150),
    ParentID INT );

INSERT INTO Software VALUES (1,'Main Product',NULL); 
INSERT INTO Software VALUES (2,'Next Main Product',NULL); 
INSERT INTO Software VALUES (3,'Addon Product',1); 
INSERT INTO Software VALUES (4,'Addon Product',1); 
INSERT INTO Software VALUES (5,'Sub-Addon Product',3);

I need to create a query that will sort the data by picking the first Main Product, then finding any addon products where the parent ID matches the ID of the parent, then seeing if that addon-product has any sub-addon products.

The Query I've tried based on one of the recommendations is

WITH x AS (
    SELECT ID
    FROM Software
    WHERE ParentID IS NULL)
SELECT 
    Software.ID,
    Software.name
FROM Software
INNER JOIN x ON x.ID=Software.ParentID OR x.ID = Software.ID

The output however misses the sub-addon product shown below:

/---------------------
| ID  | Name
| 1   | Main Product
| 3   |   Addon Product
| 4   |   Addon Product
| 2   | Main Product
\----------------------

What I need is this where the sub-addon is added

/---------------------
| ID  | Name
| 1   | Main Product
| 3   |   Addon Product
| 5   |     Sub-Addon Product
| 4   |   Addon Product
| 2   | Next Main Product
\----------------------
3

As far as you want to obtain all records where parent_id is null, I'd suggest to use an inline udf function.

-- Inline UDF function
CREATE FUNCTION fnTree(@ID int)
RETURNS table AS
RETURN
(
    WITH tree AS
    (
      SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast(c1.id as varchar(100))
      FROM dbo.[software] c1
      WHERE c1.id = @ID
      UNION ALL
      SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1, Path=Cast(tree.path+'/'+cast(c2.id as varchar(10)) as varchar(100))
      FROM dbo.[software] c2 INNER JOIN tree ON tree.id = c2.parent_id
    )
    SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name as name
    FROM tree
);
GO

Then you can CROSS APPLY this function with all records where parent_id is null Take care when using a recursive function, you should set OPTION (MAXRECURSION XXX) just to ensure it can hold all returned records.

SELECT      f.id, f.name, f.parent_id, f.path
FROM        software
CROSS APPLY fnTree(software.id) f
WHERE       software.parent_id IS NULL
ORDER BY    f.path
OPTION (MAXRECURSION 0);
GO
id | name                  | parent_id | path 
-: | :-------------------- | --------: | :----
 1 | Main Product          |      null | 1    
 3 |   Addon Product       |         1 | 1/3  
 5 |     Sub-Addon Product |         3 | 1/3/5
 4 |   Addon Product       |         1 | 1/4  
 2 | Next Main Product     |      null | 2    

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.