https://www.youtube.com/watch?v=EsGPj8Ojk2o&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=17

-- Create the people table
CREATE TABLE people (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(20),
    gender CHAR(2)
);

-- Create the relations table with foreign keys referencing the people table
CREATE TABLE relations (
    c_id INT,
    p_id INT,
    FOREIGN KEY (c_id) REFERENCES people(id),
    FOREIGN KEY (p_id) REFERENCES people(id)
);

-- Insert values into the people table
INSERT INTO people (id, name, gender) VALUES
    (107, 'Days', 'F'),
    (145, 'Hawbaker', 'M'),
    (155, 'Hansel', 'F'),
    (202, 'Blackston', 'M'),
    (227, 'Criss', 'F'),
    (278, 'Keffer', 'M'),
    (305, 'Canty', 'M'),
    (329, 'Mozingo', 'M'),
    (425, 'Nolf', 'M'),
    (534, 'Waugh', 'M'),
    (586, 'Tong', 'M'),
    (618, 'Dimartino', 'M'),
    (747, 'Beane', 'M'),
    (878, 'Chatmon', 'F'),
    (904, 'Hansard', 'F');

-- Insert values into the relations table
INSERT INTO relations (c_id, p_id) VALUES
    (145, 202),
    (145, 107),
    (278, 305),
    (278, 155),
    (329, 425),
    (329, 227),
    (534, 586),
    (534, 878),
    (618, 747),
    (618, 904);

WITH

-- Approach 1
WITH Male_parents(f_id,c_id,`Father's name`) AS (
SELECT id AS f_id,c_id,name AS `Father's name` FROM relations r
LEFT JOIN people p ON r.p_id = p.id
WHERE Gender = 'M'
)
,Female_parents (M_id,c_id,`Mother's name`) AS (
SELECT id AS M_id,c_id,name `Mother's name` FROM relations r
INNER JOIN people p ON r.p_id = p.id
WHERE Gender = 'F'
)
SELECT name,`Father's name`,`Mother's name` FROM Male_parents Mp INNER JOIN people p ON mp.c_id = p.id
INNER JOIN Female_parents fp ON fp.c_id = p.id;

-- Approach 2
SELECT 
    p.name AS PersonName,
    MAX(m.name) AS Mothername,
    MAX(f.name) AS Fathername
FROM
    relations r
        LEFT JOIN
    people m ON r.p_id = m.id AND Gender = 'F'
        LEFT JOIN
    people f ON r.p_id = f.id AND f.Gender = 'M'
        INNER JOIN
    people p ON c_id = p.id
GROUP BY c_id;

CASE WHEN

-- Approach 3

SELECT 
    p1.name AS PersonName,
    MAX(CASE
        WHEN p.Gender = 'F' THEN p.name
    END) AS Mothername,
    MAX(CASE
        WHEN p.Gender = 'M' THEN p.name
    END) AS Fathername
FROM
    relations r
        INNER JOIN
    people p ON r.p_id = p.id
        JOIN
    people p1 ON r.c_id = p1.id
GROUP BY c_id;

-- Max used to get rid of nulls.