<!-- --> > select version(); > > <pre> > | 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 | > </pre> <!-- --> > create table company > ( company_name text not null, > job text not null, > name text not null, > primary key (name, company_name, job) > ) ; > > <pre> > ✓ > </pre> <!-- --> > 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') ; > > <pre> 7 rows affected > </pre> <!-- --> > 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' ; > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3712fe87a7dae6ba8b66f6abaade62d1)*