CODEWITHSUNDEEP
sql-serversql-server-2008-r2
basodre
basodre

Reputation: 5770

SQL Recursive Query to Navigate Up and Return Top Item

I have a hierarchical table used to store part builds. The schema is a simple one such that the table stores a component and its parent. |--Component--|--Parent--|. I'm using a Common Table Expression to build the part list from the parent parts, and this is working without issue.

The problem I'm running into is in trying to determine the top-level part from an internal component. I've tweaked the CTE to build up a part list from an internal part, but I'm running into trouble returning only the parent part.

This might be easier to explain using an example, so please see below. In the example, Car and Motorcycle are the two top-level parts. The only way to determine this is that they are found in the Parent column, but never in the Component column (I don't have control over the schema or the way the data is stored).

CREATE TABLE Build
(
    Parent      VARCHAR(20),
    Component   VARCHAR(20)
);

INSERT INTO Build
VALUES
('Car', 'Door'),
('Door', 'Handle'),
('Handle', 'Screw'),
('Motorcycle', 'Frame'),
('Frame', 'Tank'),
('Tank', 'Internals'),
('Internals', 'Screw');

DECLARE @Comp VARCHAR(20) = 'Screw';

WITH UsedPart (Parent, Component, Level)
AS
(
    SELECT
        b.Parent,
        b.Component,
        1
    FROM Build as b
    WHERE b.Component = @Comp
    UNION ALL
    SELECT
        b.Parent,
        b.Component,
        u.Level + 1
    FROM Build as b
    INNER JOIN UsedPart as u
    ON b.Component = u.Parent
)

SELECT
    *
FROM UsedPart

This returns:

Parent      Component   Level
----------------------------------------
Handle      Screw           1
Internals   Screw           1
Tank        Internals       2
Frame       Tank            3
Motorcycle  Frame           4    <-- Return this
Door        Handle          2
Car         Door            3    <-- Return this

The only solution I've come up with is to JOIN the above result set with a subquery like SELECT fparent FROM Build WHERE fparent NOT IN Component but it just seems to be an inefficient solution to the problems.

Upvotes: 0

Views: 193

Answers (3)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Try this:

select Parent
from build
where Parent not in (select Component from build)

Upvotes: 0

PeterHe
PeterHe

Reputation: 2766

You can just change the select to:

SELECT u.*
FROM UsedPart u
WHERE NOT EXISTS(SELECT 1 FROM UsedPart p WHERE p.Component=u.Parent)

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Here is one way you could tackle this. I am sure there are others.

DECLARE @Comp VARCHAR(20) = 'Screw';

WITH UsedPart
AS
(
    SELECT
        b.Parent,
        b.Component,
        Level = 1,
        RowNum = ROW_NUMBER() over(order by parent)
    FROM Build as b
    WHERE b.Component = @Comp
    UNION ALL
    SELECT
        b.Parent,
        b.Component,
        u.Level + 1,
        u.RowNum
    FROM Build as b
    INNER JOIN UsedPart as u
    ON b.Component = u.Parent
)
, SortedValues as
(
    SELECT *
        , MyVal = ROW_NUMBER() over(partition by RowNum order by Level desc)
    FROM UsedPart
)

select *
from SortedValues
where MyVal = 1

Upvotes: 1

Related Questions