How to select title using parent and child hierarchy in sql?

Question

I need to search title '%p%'. if i check title ,parent tile and status as parent and child level. if the searched title parent visible is true then show the child Title.

SELECT title
FROM table1 t1
JOIN table1 t2 ON t1.title = t2.parenttitle AND t1.visible = TRUE
JOIN table1 t3 ON t3.title = t2.parenttitle AND t3.visible = TRUE
WHERE t1.title LIKE '%p%'

Table1

Title   | ParentTitle|visible   
P1        Home         TRUE 
p1.1       p1          TRUE 
p1.2       p1          FALSE    
p1.3       p1.2        TRUE 
p1.3.1     p1.3        TRUE 
p2         Home        TRUE 
p2.1       p2          TRUE 
p2.2       p2.1        FALSE    
P3         Home        TRUE 
p3.1       p3          TRUE 
P3.1.1     p3.1        FALSE

I need output like

title
p1
p1.1
p2
p2.1
P3
p3.1

Use a `hierarchyid` column instead of trying to find the parent using a title. Using a title as a key isn't a good idea anyway — Panagiotis Kanavos, Oct 19 '16 at 07:16
Try googling for `hierarhcyid`. SQL Server has excelent documentation with a lot of examples and many functions to handle hierarchies, retrieve child or parent nodes etc. I think the second result is the [tutorial](https://msdn.microsoft.com/en-us/library/bb677213.aspx) on how to [convert an existing table](https://msdn.microsoft.com/en-us/library/bb677237.aspx) and [query a hierarchical table](https://msdn.microsoft.com/en-us/library/bb677270.aspx), including [sample queries](https://msdn.microsoft.com/en-us/library/bb677191.aspx) to find parents, children, roots, level etc — Panagiotis Kanavos, Oct 19 '16 at 07:27
Possible duplicate of [Sql server CTE and recursion example](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) — neer, Oct 19 '16 at 07:35

score 1 · Accepted Answer · edited Feb 24 '17 at 10:49

CREATE TABLE CTE(
    Title VARCHAR(20),
    ParentTitle VARCHAR(20),
    visible VARCHAR(20),    
)   

INSERT INTO CTE
VALUES
('p1', 'Home', 'TRUE'),
('p1.1', 'p1', 'TRUE'),
('p1.2', 'p1', 'FALSE'),    
('p1.3', 'p1.2', 'TRUE'), 
('p1.3.1', 'p1.3', 'TRUE'), 
('p2', 'Home', 'TRUE'), 
('p2.1', 'p2', 'TRUE'), 
('p2.2', 'p2.1', 'FALSE'),    
('P3', 'Home', 'TRUE'), 
('p3.1', 'p3', 'TRUE'), 
('P3.1.1', 'p3.1', 'FALSE')


; WITH YTE AS
(
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY LEFT(A.Title, 2) ORDER BY A.Title) AS RN,
    DENSE_RANK() OVER(ORDER BY LEFT(A.Title, 2)) AS DR
    FROM CTE A
)
, ZTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY LEFT(A.Title, 2) ORDER BY A.Title) AS RNFalse
    FROM YTE A 
    WHERE A.visible = 'FALSE'
)
, ATE AS 
(
    SELECT A.Title
    FROM YTE A
    INNER JOIN ZTE B ON A.DR = B.DR AND A.RN < B.RN
    WHERE RNFalse = 1
) SELECT * FROM ATE

/*
Output:
p1
p1.1
p2
p2.1
P3
p3.1
*/

If you don't want to use a common table expression, then use subqueries

SELECT A.Title FROM 
(
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY LEFT(A.Title, 2) ORDER BY A.Title) AS RN
    FROM CTE A
) AS A
INNER JOIN
(
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY LEFT(A.Title, 2) ORDER BY A.Title) AS RNFalse
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER(PARTITION BY LEFT(A.Title, 2) ORDER BY A.Title) AS RN
        FROM CTE A
    ) A 
    WHERE A.visible = 'FALSE'
) AS B ON LEFT(A.Title, 2) = LEFT(B.Title, 2) AND A.RN < B.RN
WHERE B.RNFalse = 1

So if you want to remove ROW_NUMBER also then you have to compare varchar which is not good. But no other way you left for me. Try this,

SELECT A.Title FROM CTE A
INNER JOIN 
(
     SELECT LEFT(Title, 2) AS TitleGroup, Min(Title) AS Title
     FROM CTE
     WHERE visible = 'False'
     GROUP BY LEFT(Title, 2)
) B ON LEFT(A.Title, 2) = B.TitleGroup 
AND A.Title < B.Title --Comparing String like this is not good

Thanks @Tanjim Rahman.....you can help me pls.....possible to get without recursive... — Meline, Oct 19 '16 at 08:52
I am using joins. If you are allergic to common table expression then just use it as simple sub quires. Using CTE doesn't mean recursion. Please clarify what is your plan? — Esty, Oct 19 '16 at 09:47
Thanks..@Tanjim Rahman.. yes am expected this.updated..answer....If possible without Row number and Dense rank.... — Meline, Oct 19 '16 at 11:18
See update. `DENSE_RANK` removed. But `ROW_NUMBER` not possible. May I know why you don't wanna use `Common Table Expression` & `Window Function` ? — Esty, Oct 19 '16 at 11:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/126114/discussion-between-tanjim-rahman-and-meline). — Esty, Oct 19 '16 at 11:22
Select b.Title,b.ParentTitle from CTE B left join CTE C ON C.ParentTitle=b.Title AND b.visible='TRue' where b.ParentTitle in(Select a.ParentTitle from CTE A join CTE B on a.Title=b.ParentTitle and b.visible='false' )...like this..way...if possible.... — Meline, Oct 20 '16 at 05:38

How to select title using parent and child hierarchy in sql?

1 Answers1