CODEWITHSUNDEEP
sqlsql-serverhierarchical-datarecursive-query
Meline
Meline

Reputation: 119

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

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

Upvotes: 0

Views: 332

Answers (1)

Esty
Esty

Reputation: 1912

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

Upvotes: 1

Related Questions