SQL problem

sqlproblem.md

Background

I'm writing a query to generate a page that will show people involved in entertainment who they are connected to through a social graph. I may end up using Neo4j for it, but I do want to at least attempt it using PostgreSQL.

Schema

Here is a simplified schema:

Person
id
name

Represents a person

Project
id
name

A film, TV show or commercial

Credit
id
person_id
role

Links a person to a project

What I want to show

I want to generate a page to show how people are connected to each other. It will show the current_person's connections to a given person. I can generate a list of mutual projects through the following query:

-- given my_person_id and other_person_id

SELECT DISTINCT p.*
  FROM projects p
  JOIN credits c ON p.id = c.project_id
  WHERE c.person_id = my_person_id
INTERSECT
  SELECT DISTINCT p.* FROM projects p
  JOIN credits c ON p.id = c.project_id
  WHERE c.person_id = other_person_id

However that will only show projects they both worked on. If someone worked on a project with someone that worked on a project with someone else, I would like to display that information as well as through a second intermediary. (you worked with fred who worked with jim who worked with him). However, I don't need to show any more information than that.

This should be possible using a CTE inside PostgreSQL.