clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799367 fiddles created (41676 in the last week).

select version();
version
PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
version
PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
 hidden batch(es)


create table company ( company_name text not null, job text not null, name text not null, primary key (name, company_name, job) ) ;
 hidden batch(es)


insert into company (name, company_name, job) values ('Bob Ross', 'Painters', 'director'), ('Carl', 'Painters', 'manager'), ('Carl', 'Sunny-D', 'administrator'), ('Celine Dion', 'Sunny-D', 'director'), ('Johnny', 'Painters', 'worker'), ('Johnny', 'X-factory', 'worker'), ('Celine Dion', 'X-factory', 'director') ;
7 rows affected
7 rows affected
 hidden batch(es)


WITH RECURSIVE conn AS ( SELECT name, company_name, job, (ROW_NUMBER() OVER (ORDER BY company_name, job))::text AS rn, CONCAT_WS(', ', name, company_name, job)::text AS node, 1 AS lvl FROM company WHERE name = 'Bob Ross' UNION ALL SELECT b.name, b.company_name, b.job, CONCAT(a.rn, '-', (ROW_NUMBER() OVER (PARTITION BY a.rn ORDER BY b.name, b.company_name, b.job))::text), CONCAT(a.node, ' - ', CONCAT_WS(', ', b.name, b.company_name, b.job)), lvl + 1 FROM conn AS a JOIN company AS b ON ( ( a.company_name = b.company_name AND a.name <> b.name AND a.lvl % 2 = 1 ) OR ( a.company_name <> b.company_name AND a.name = b.name AND a.lvl % 2 = 0 ) ) AND a.name <> 'Celine Dion' AND b.name <> 'Bob Ross' ) SELECT r.* FROM conn AS f JOIN conn AS r ON f.rn LIKE CONCAT(r.rn, '%') WHERE f.name = 'Celine Dion' ;
name company_name job rn node lvl
Bob Ross Painters director 1 Bob Ross, Painters, director 1
Carl Painters manager 1-1 Bob Ross, Painters, director - Carl, Painters, manager 2
Carl Sunny-D administrator 1-1-1 Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator 3
Celine Dion Sunny-D director 1-1-1-1 Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator - Celine Dion, Sunny-D, director 4
Bob Ross Painters director 1 Bob Ross, Painters, director 1
Johnny Painters worker 1-2 Bob Ross, Painters, director - Johnny, Painters, worker 2
Johnny X-factory worker 1-2-1 Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker 3
Celine Dion X-factory director 1-2-1-1 Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker - Celine Dion, X-factory, director 4
name company_name job rn node lvl
Bob Ross Painters director 1 Bob Ross, Painters, director 1
Carl Painters manager 1-1 Bob Ross, Painters, director - Carl, Painters, manager 2
Carl Sunny-D administrator 1-1-1 Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator 3
Celine Dion Sunny-D director 1-1-1-1 Bob Ross, Painters, director - Carl, Painters, manager - Carl, Sunny-D, administrator - Celine Dion, Sunny-D, director 4
Bob Ross Painters director 1 Bob Ross, Painters, director 1
Johnny Painters worker 1-2 Bob Ross, Painters, director - Johnny, Painters, worker 2
Johnny X-factory worker 1-2-1 Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker 3
Celine Dion X-factory director 1-2-1-1 Bob Ross, Painters, director - Johnny, Painters, worker - Johnny, X-factory, worker - Celine Dion, X-factory, director 4
 hidden batch(es)