Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable
    ("p_id" int, "parent_name" text, "s_id" int, "student_name" text);
    
INSERT INTO mytable VALUES
    (1, 'John Doe', 100, 'Mike Doe'),
    (3, 'Jane Doe', 100, 'Mike Doe'),
    (3, 'Jane Doe', 105, 'Lisa Doe'),
    (5, 'Will Willy', 108, 'William Son');
    
SELECT * FROM mytable;

CREATE TABLE



INSERT 0 4




p_id
parent_name
s_id
student_name


1
John Doe
100
Mike Doe

3
Jane Doe
100
Mike Doe

3
Jane Doe
105
Lisa Doe

5
Will Willy
108
William Son



SELECT 4


SELECT
    s_id,
    array_agg(p_id) as parents
FROM mytable
GROUP BY s_id


s_id
parents


108
{5}

105
{3}

100
{1,3}



SELECT 3


SELECT
    *
FROM (
    SELECT
        s_id,
        array_agg(p_id) as parents
    FROM mytable
    GROUP BY s_id
) s JOIN mytable t ON t.p_id = ANY(s.parents)


s_id
parents
p_id
parent_name
s_id
student_name


100
{1,3}
1
John Doe
100
Mike Doe

105
{3}
3
Jane Doe
100
Mike Doe

100
{1,3}
3
Jane Doe
100
Mike Doe

105
{3}
3
Jane Doe
105
Lisa Doe

100
{1,3}
3
Jane Doe
105
Lisa Doe

108
{5}
5
Will Willy
108
William Son



SELECT 6


SELECT DISTINCT ON (t.s_id)
    *
FROM (
    SELECT
        s_id,
        array_agg(p_id) as parents
    FROM mytable
    GROUP BY s_id
) s JOIN mytable t ON t.p_id = ANY(s.parents)
ORDER BY t.s_id, CARDINALITY(parents) DESC


s_id
parents
p_id
parent_name
s_id
student_name


100
{1,3}
1
John Doe
100
Mike Doe

100
{1,3}
3
Jane Doe
105
Lisa Doe

108
{5}
5
Will Willy
108
William Son



SELECT 3


SELECT
    array_agg(parent_name) as parents,
    array_agg(student_name) as students
FROM (
    SELECT DISTINCT ON (t.s_id)
        *
    FROM (
        SELECT
            s_id,
            array_agg(p_id) as parents
        FROM mytable
        GROUP BY s_id
    ) s JOIN mytable t ON t.p_id = ANY(s.parents)
    ORDER BY t.s_id, CARDINALITY(parents) DESC
) s
GROUP BY parents


parents
students


{"John Doe","Jane Doe"}
{"Mike Doe","Lisa Doe"}

{"Will Willy"}
{"William Son"}



SELECT 2

p_id	parent_name	s_id	student_name
1	John Doe	100	Mike Doe
3	Jane Doe	100	Mike Doe
3	Jane Doe	105	Lisa Doe
5	Will Willy	108	William Son

s_id	parents	p_id	parent_name	s_id	student_name
100	{1,3}	1	John Doe	100	Mike Doe
105	{3}	3	Jane Doe	100	Mike Doe
100	{1,3}	3	Jane Doe	100	Mike Doe
105	{3}	3	Jane Doe	105	Lisa Doe
100	{1,3}	3	Jane Doe	105	Lisa Doe
108	{5}	5	Will Willy	108	William Son

parents	students
{"John Doe","Jane Doe"}	{"Mike Doe","Lisa Doe"}
{"Will Willy"}	{"William Son"}