none
sql graph query output

    Question

  • Team,

    I am looking for a output as stated below :

    Required output :

    Roshan > Mitran > Romba > Gopal
    Roshan > Gopal > Sada > Mrithu > Kumar > Vadi > Gokul
    Roshan > Gopal > Sada > Mrithu > Indigo
    Roshan > Romba > Jag > Naresh
    Roshan > Mitran > Kumar
    Roshan > Mitran > Surabh
    Roshan > Mitran > Akala
    Roshan > Mitran > Dhoni
    Roshan > Mitran > Watson

    Below are a sample graph database created for example. You can run this query in SQL Server 2017 and create the base table, node table and edge table. Also you can load data using below queries. I need help in getting output as stated in Required output : above. I am seeking SQL query from the forum to get the above output. Advanced thanks to all of you.

    DROP TABLE IF EXISTS Kids;

    CREATE TABLE Kids
    (RoomNo  NUMERIC(8) not null,
    RoomName VARCHAR(20) NOT NULL,
    RoomLink NUMERIC(8),
    )


    INSERT INTO Kids values
    (1,'Roshan',NULL),
    (14,'Mitran',1),
    (6,'Kumar',14),
    (7,'Surabh' ,14),
    (8,'Akala',14),
    (9,'Dhoni',14),
    (10,'Watson',14),
    (2,'Romba',1),
    (3,'Jag',2),
    (31,'Naresh',3),
    (4,'Gopal',1),
    (12,'Sada',4),
    (5,'Mirchi',12),
    (13,'Mrithu',12),
    (11,'Indigo',13),
    (15,'Kumar',13),
    (16,'Vadi',15),
    (17,'Gokul',16);



    DROP TABLE IF EXISTS RoomNode;

    CREATE TABLE RoomNode(
    RoomNo   NUMERIC(8) NOT NULL,
    Name VARCHAR(40),
    RoomLink NUMERIC(8)
    ) AS NODE;



    INSERT INTO RoomNode(RoomNo,NAME,RoomLink) select RoomNo,RoomName,RoomLink  from Kids



    DROP TABLE IF EXISTS RoomEdge;

    CREATE TABLE RoomEdge(RoomNum numeric(8)) AS EDGE

    INSERT INTO RoomEdge 
    SELECT e.$node_id, m.$node_id ,e.RoomNo 
    FROM dbo.RoomNode e 
    inner JOIN dbo.RoomNode m 
    ON e.RoomNo = m.RoomLink;

    samedi 26 mai 2018 04:22

Toutes les réponses

  • what is the rule for getting the above output?

    Looks like mixed case of few rules being applied

    Also feel like one row has been missed out

    Anyways here's my stab at this based on what I understood from your illustration above

    DROP TABLE IF EXISTS Kids;
    
    CREATE TABLE Kids
    (RoomNo  NUMERIC(8) not null,
    RoomName VARCHAR(20) NOT NULL,
    RoomLink NUMERIC(8),
    )
    
    
    INSERT INTO Kids values
    (1,'Roshan',NULL),
    (14,'Mitran',1),
    (6,'Kumar',14),
    (7,'Surabh' ,14),
    (8,'Akala',14),
    (9,'Dhoni',14),
    (10,'Watson',14),
    (2,'Romba',1),
    (3,'Jag',2),
    (31,'Naresh',3),
    (4,'Gopal',1),
    (12,'Sada',4),
    (5,'Mirchi',12),
    (13,'Mrithu',12),
    (11,'Indigo',13),
    (15,'Kumar',13),
    (16,'Vadi',15),
    (17,'Gokul',16);
    
    
    
    DROP TABLE IF EXISTS RoomNode;
    
    CREATE TABLE RoomNode(
    RoomNo   NUMERIC(8) NOT NULL,
    Name VARCHAR(40),
    RoomLink NUMERIC(8)
    ) AS NODE;
    
    
    
    INSERT INTO RoomNode(RoomNo,NAME,RoomLink) select RoomNo,RoomName,RoomLink  from Kids
    
    
    
    DROP TABLE IF EXISTS RoomEdge;
    
    CREATE TABLE RoomEdge(RoomNum numeric(8)) AS EDGE
    
    INSERT INTO RoomEdge 
    SELECT e.$node_id, m.$node_id ,e.RoomNo 
    FROM dbo.RoomNode e 
    inner JOIN dbo.RoomNode m 
    ON e.RoomNo = m.RoomLink;
    
    --SELECT * FROM RoomNode
    --SELECT * FROM RoomEdge
    
    ;With CTE
    AS
    (
    SELECT r1.NAME AS Parent,r2.NAME AS Child,CAST(CONCAT(r1.NAME,'->',r2.NAME) AS varchar(max)) AS Path,r1.$node_id AS parentid, r2.$node_id as childid,1 as lvl
    FROM RoomNode r1 
    JOIN RoomEdge e
    ON e.$from_id = r1.$node_id
    JOIN RoomNode r2
    ON r2.$node_id = e.$to_id
    AND r1.NAME = 'Roshan'
    UNION ALL
    SELECT c.Child,r.NAME,CAST(CONCAT(c.Path,'->',r.NAME) AS varchar(max)),c.childid,r.$node_id,lvl + 1
    FROM CTE c
    JOIN RoomEdge e
    ON e.$from_id = c.childid
    JOIN RoomNode r
    ON r.$node_id = e.$to_id
    ),
    CTE1 
    AS
    (
    SELECT Parent,Child,Path,lvl
    FROM CTE
    WHERE lvl = 1
    UNION ALL
    SELECT Parent,Child,Path,lvl
    FROM
    (
    SELECT c1.Parent,c1.Child,c1.Path,c1.lvl,
    DENSE_RANK() OVER (PARTITION BY c2.Child ORDER BY c1.Lvl DESC) AS Rnk
    FROM CTE c1
    JOIN CTE c2
    ON '->' + c1.Path + '->' LIKE '%->' + c2.Child + '->%'
    WHERE c2.Lvl = 1
    )r
    WHERE Rnk = 1
    )
    SELECT [Path]
    FROM
    (
    SELECT CASE WHEN Lvl = 1 THEN CONCAT(Parent,'->',STUFF((SELECT '->' + Child FROM CTE1 WHERE Parent = c.Parent FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,''))
    ELSE Path END AS Path,Lvl
    FROM CTE1 c
    UNION 
    SELECT c1.Path,c1.Lvl
    FROM CTE c1
    JOIN CTE c2
    ON '->' + c1.Path + '->' LIKE '%->' + c2.Parent + '->%'
    LEFT JOIN CTE1 c3
    ON '->' + c3.Path + '->' LIKE '%->' + c1.Child + '->%'
    WHERE c3.Child IS NULL
    AND  c2.Lvl = 1
    )t
    ORDER BY  Lvl
    
    
    /*
    Output
    ------------------------------------------
    Path
    ------------------------------------------
    Roshan->Mitran->Romba->Gopal
    Roshan->Mitran->Akala
    Roshan->Mitran->Dhoni
    Roshan->Mitran->Kumar
    Roshan->Mitran->Surabh
    Roshan->Mitran->Watson
    Roshan->Gopal->Sada->Mirchi
    Roshan->Romba->Jag->Naresh
    Roshan->Gopal->Sada->Mrithu->Indigo
    Roshan->Gopal->Sada->Mrithu->Kumar->Vadi->Gokul
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    samedi 26 mai 2018 07:21