Skip to content

Instantly share code, notes, and snippets.

@paul
Created January 17, 2011 21:28
Show Gist options
  • Save paul/783520 to your computer and use it in GitHub Desktop.
Save paul/783520 to your computer and use it in GitHub Desktop.
WITH RECURSIVE descendants(id, active, name, longname, parent_id, reseller, created_at, updated_at, uuid) AS (
SELECT * FROM client WHERE {conditions...}
UNION
SELECT client.* FROM client, descendants WHERE client.parent_id = descendants.id
)
SELECT * FROM descendants
-- OR --
SELECT * FROM clients WHERE id IN (
WITH RECURSIVE descendants(id, parent_id) AS (
SELECT id, parent_id FROM clients WHERE {conditions...}
UNION
SELECT clients.id, clients.parent_id FROM clients, descendants WHERE clients.parent_id = descendants.id
)
SELECT id FROM descendants
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment