Shortest "path" between two employees across companies, PostgreSQL

Shortest "path" between two employees across companies

-- SO question http://stackoverflow.com/questions/43166330/recusive-sql-query/43167090#43167090
CREATE TABLE CompanyInfo (
  company text,
  role text,
  employee text,
  primary key (company, role, employee)
);

insert into CompanyInfo values('Company A', 'CEO', 'Joe');
insert into CompanyInfo values('Company A', 'Board member', 'Alex');
insert into CompanyInfo values('Company B', 'CEO', 'Alex');
insert into CompanyInfo values('Company B', 'Board member', 'Peter');
insert into CompanyInfo values('Company C', 'CEO', 'Sue');
insert into CompanyInfo values('Company C', 'Board member', 'Peter');

WITH RECURSIVE shortest_path(c1,p1,c2,p2, path) AS (
  -- Basecase --
  SELECT c1.company, c1.employee, c2.company, c2.employee, array[c1.employee, c2.employee]
  FROM CompanyInfo c1 
  JOIN CompanyInfo c2 ON c1.company = c2.company
      AND c1.employee = 'Joe'
      AND c1.employee <> c2.employee
  UNION ALL
  -- Recursive --
  SELECT c1, p1, c3.company, c3.employee, path || c3.employee
  FROM shortest_path c1
  JOIN CompanyInfo c2 ON c1.p2 = c2.employee    
  JOIN CompanyInfo c3 ON c3.company = c2.company
      AND NOT (c3.employee = ANY(c1.path) ) --OR 'Sue'= ANY(c1.path))
)

SELECT *, array_length(path,1) -1 as distance
FROM shortest_path
WHERE p2 = 'Sue'
ORDER BY distance 
LIMIT 1;


run \| edit \| history \| help	0

Please log in to post a comment.