Finding all children for multiple parents in single SQL query

Question

Suppose I have a table with parent-child relationships.

parent  child
1       4
1       5
2       6
3       7
4       8
6       9
7       10
8       11

Now I have a query that returns a list of people (eg. 1 and 2) and I want to find all their children, grandchildren etc. (in this case: 4, 5, 6, 8, 9, 11).

I know I can use common table expressions to search recursively, but I wondered if I could create a SQL statement to find all descendents at once without having to iterate over the input set.

Edit: sorry for not being clear enough. I'm looking for something like:

SELECT {Hierarchical relation} from table where parent in (1,2)

which should result in a single output column with rows for 4, 5, 6, 8, 9, 11.

I'm no longer interested in the relationship in the output, just the complete set of family members for multiple families.

Answer 1

Here it is

---- PlainTable ----
parent  idElement (child)
Null    1
1       4
1       5
2       6
3       7
4       8
6       9
7       10
8       11

WITH tableR (parent, idElement)
AS
(
-- Anchor member definition
    SELECT e.parent, e.idElement
    FROM PlainTable AS e   
    WHERE parent in (1,2)
    UNION ALL
-- Recursive member definition
    SELECT e.parent, e.idElement
    FROM PlainTable AS e
    INNER JOIN tableR AS d
        ON e.parent = d.idElement
)
-- Statement that executes the CTE
SELECT idElement
FROM tableR  --inner join to plain table by id if needed