sql graph query output

• mj 89387

  SSC Enthusiast

  Points: 175

  More actions

  May 25, 2018 at 10:23 pm

  #405882

  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;

• Jeff Moden

  SSC Guru

  Points: 1004372

  More actions

  May 26, 2018 at 11:22 am

  #1992058

  Not trying to be difficult here.  I'm trying to anticipate your next question after this. 😉

  What will you do with the output you require once someone has shown you how to do this?  And are the node and edge tables actually required because they're just extra overhead for this problem and the half dozen or so follow up problems that I'm thinking of.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)