Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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.
postgresql select test
m,,nm,
arjun
Q3
PostreSQL: Left Join
ds
Many to many
avg with subquery
Correlated subquery in SELECT clause, and rewritten for Netezza
FinaleNFJNDFJKSN
Please log in to post a comment.