Re: How to retrieve this data as a single row with three new columns

[Rick James]

Here's one way:

SELECT  f.child_id, f.mother_id, f.father_id,
        f.address, f.household_income,
        uc.name AS child_name,
        um.name AS mother_name,
        uf.name AS father_name
    FROM  family AS f
    JOIN  user AS uc ON uc.user_id = f.child_id
    JOIN  user AS um ON um.user_id = f.mother_id
    JOIN  user AS uf ON uf.user_id = f.father_id;