Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE relation (cid int, pid int);
INSERT ALL 
    INTO relation (cid, pid)
         VALUES (101, 202)
    INTO relation (cid, pid)
         VALUES (566, 322)
    INTO relation (cid, pid)
         VALUES (875, 345)
SELECT * FROM dual;
3 rows affected

CREATE TABLE people (id int, name varchar2(6), gender varchar2(1));
INSERT ALL 
    INTO people (id, name, gender)
         VALUES (101, 'Riya', 'F')
    INTO people (id, name, gender)
         VALUES (566, 'Aman', 'M')
    INTO people (id, name, gender)
         VALUES (202, 'Rakesh', 'M')
    INTO people (id, name, gender)
         VALUES (875, 'lucky', 'M')
    INTO people (id, name, gender)
         VALUES (202, 'Reena', 'F')
    INTO people (id, name, gender)
         VALUES (322, 'Raina', 'F')
    INTO people (id, name, gender)
         VALUES (345, 'Rohit', 'M')
    INTO people (id, name, gender)
         VALUES (322, 'Mohit', 'M')
    INTO people (id, name, gender)
         VALUES (345, 'Meena', 'F')
SELECT * FROM dual;
9 rows affected

SELECT c.name Child, 
       MAX(CASE WHEN p.gender = 'F' THEN p.name END) Mother, 
       MAX(CASE WHEN p.gender = 'M' THEN p.name END) Father
FROM relation r
INNER JOIN people c ON c.id = r.cid
INNER JOIN people p ON p.id = r.pid
GROUP BY r.cid, c.name;


CHILD
MOTHER
FATHER


Riya
Reena
Rakesh

Aman
Raina
Mohit

lucky
Meena
Rohit