CODEWITHSUNDEEP
sqlsql-servert-sql
VJ87
VJ87

Reputation: 142

SQL - find parent in same column

I cant express the problem correctly to find the answer online so i'm hoping someone could provide me with a link to a solution because i think this is a rather common task.

We have a hierachy of products and want to determine the parents. All product names are in the same column and the logic is the following

ProductId ProductName
-----------------------------------------------------------------------------
1         ABC
2         ABCD
3         ABCD1
4         ABCD2

Result should be

ABCD1 & ABCD2 are children of ABCD and ABCD is child of ABC

ProductId ProductName ParentName ParentId
------------------------------------------------------------------------------
1         ABC         NULL       NULL
2         ABCD        ABC        1
3         ABCD1       ABCD       2
...

Upvotes: 1

Views: 124

Answers (5)

Ftaveras
Ftaveras

Reputation: 719

You can use Common Table Expression (CTE) to do the job.

with product_table (ProductId, ProductName) as
(
    select 1 ProductId         , 'ABC' ProductName union all
    select 2 ProductId         , 'ABCD' ProductName union all
    select 3 ProductId         , 'ABCD1' ProductName union all
    select 4 ProductId         , 'ABCD2' ProductName --union all
)
,product_result (ProductId, ProductName, ParentName, ParentId) as
(
    select ProductId, ProductName, convert(varchar,null) ParentName,     convert(int, null) ParentId
    from product_table 
    where ProductName = 'ABC' --start with 
    union all
    select d.ProductId, d.ProductName, convert(varchar,p.ProductName)     ParentName, p.ProductId ParentId
    from product_table d
    , product_result p
    where d.ProductName like p.ProductName+'_'
)
select *
from product_result

The first part product_table must be replaced by your own product table. It is used here to generate a tempory dataset.

Your final query will look like:

with product_result (ProductId, ProductName, ParentName, ParentId) as
(
    select ProductId, ProductName, convert(varchar,null) ParentName,     convert(int, null) ParentId
    from <YOUR_PRODUCT_TABLE_GOES_HERE> 
    where ProductName = 'ABC' --start with 
    union all
    select d.ProductId, d.ProductName, convert(varchar,p.ProductName)     ParentName, p.ProductId ParentId
    from <YOUR_PRODUCT_TABLE_GOES_HERE> d
    , product_result p
    where d.ProductName like p.ProductName+'_'
)
select *
from product_result

CTE is available since SQL2008. for more info WITH common_table_expression (Transact-SQL)

Upvotes: 0

LukStorms
LukStorms

Reputation: 29667

If there's only 1 character difference.
Then you can LEFT JOIN to the ProductName & one wildcard character '_'

SELECT 
 p1.ProductId, 
 p1.ProductName, 
 p2.ProductName AS ParentName, 
 p2.ProductId AS ParentId
FROM Products p1
LEFT JOIN Products p2 ON p1.ProductName LIKE CONCAT(p2.ProductName,'_')
ORDER BY p1.ProductId;

Example snippet:

declare @Products table (
  ProductId INT primary key identity(1,1), 
  ProductName varchar(30) not null, 
  unique (ProductName)
);

insert into @Products (ProductName) values
 ('ABC')
,('ABCD')
,('ABCD1')
,('ABCD2')
;

SELECT 
 p1.ProductId, 
 p1.ProductName, 
 p2.ProductName AS ParentName, 
 p2.ProductId AS ParentId
FROM @Products p1
LEFT JOIN @Products p2 ON p1.ProductName LIKE CONCAT(p2.ProductName,'_')
ORDER BY p1.ProductId;

Result:

ProductId   ProductName ParentName  ParentId
1           ABC         NULL        NULL
2           ABCD        ABC         1
3           ABCD1       ABCD        2
4           ABCD2       ABCD        2

If it's possible that there's more than 1 character difference then:

SELECT TOP (1) WITH TIES
 p1.ProductId, 
 p1.ProductName, 
 p2.ProductName AS ParentName, 
 p2.ProductId AS ParentId
FROM Products p1
LEFT JOIN Products p2 ON p1.ProductName LIKE CONCAT(p2.ProductName,'_%')
ORDER BY ROW_NUMBER() OVER (PARTITION BY p1.ProductId ORDER BY LEN(p2.ProductName) DESC);

Upvotes: 1

SQL_M
SQL_M

Reputation: 2475

No doubt that gordon's answer is best here, but still I gave it a go as well:

USE TEMPDB

CREATE TABLE #T (ProductID INT, ProductName VARCHAR (100))
INSERT INTO #T VALUES (1, 'ABC'), (2, 'ABCD'), (3, 'ABCD1'), (4, 'ABCD2')

WITH CTE AS 
(
SELECT T.*,
      T2.ProductID AS ParentID,
      T2.ProductName AS ParentName
FROM #T AS T
CROSS JOIN #T AS T2
WHERE T.ProductName LIKE T2.ProductName + '%'
  AND T.ProductID <> T2.ProductID
)
, CTE2 AS 
(
SELECT TOP 1 T.*,
      NULL AS ParentID,
      NULL AS ParentName
FROM #T AS T
ORDER BY LEN (T.ProductName)
)

SELECT * FROM CTE UNION ALL SELECT * FROM CTE2 ORDER BY 1

Upvotes: 1

Naman Verma
Naman Verma

Reputation: 3

Did you try using Case with the condition and represent each condition as a new column. You can refer to the syntax https://www.w3schools.com/sql/sql_case.asp

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

Hmmm. I think this does what you want:

select p.*, pp.ProductName as parentName, pp.ProductId as parentId
from products p outer apply
     (select top (1) pp.*
      from products pp
      where p.ProductName like pp.ProductName + '%' and
            p.ProductId <> pp.ProductId
      order by len(pp.ProductName) desc
     ) pp;

Upvotes: 3

Related Questions