Meline Meline - 1 month ago 10
SQL Question

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

Answer
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 wanna use common table expression then use subquires

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