How to make query to show result of single recursive request?

I have the structure of the table called person, the person has: id,first_name,last_name,mother_id,father_id I want to build a simple query that shows data of person like this: id,first_name,last_name,father_first_name,mother_first_name

CREATE TABLE PERSON
(
ID varchar(20),
FIRST_NAME varchar(20),
LAST_NAME varchar(20),
PHONE_NUMBER varchar(20),
BIRTH_DATE DATE,
FATHER_ID varchar(20),
MOTHER_ID varchar(20),
PRIMARY KEY (ID),
FOREIGN KEY (FATHER_ID) REFERENCES PERSON(ID),
FOREIGN KEY (MOTHER_ID) REFERENCES PERSON(ID)
);

Answer

You can query this information simply by joining the table to itself and using a table alias. Recursion is not necessary unless the hierarchy you are navigating has a varying depth. For this question there is a fixed depth and you simply wish to see the parent child relationship between 2 records. The LEFT JOIN will ensure that even if you don’t have both of the parents in your database, the record will still show for that person.

SELECT 
    P.ID
    , P.FIRST_NAME
    , P.LAST_NAME
    , F.FIRST_NAME FATHER_FIRST_NAME
    , M.FIRST_NAME MOTHER_FIRST_NAME
FROM 
    PERSON P
LEFT JOIN 
    PERSON F ON (P.FATHER_ID = F.ID)
LEFT JOIN
    PERSON M ON (P.MOTHER_ID = M.ID)