Multiples SQL连接

Question

I have this problem and I haven't been able to figure out how can I make this work.

I have a table called "parents" and a table called "students"

Parents table has

id, name, lastname and type. "Type" cantains something like "mother" or "father".

Students table has

id, name, lastname, motherid(parents table), fatherid(parents table).

How can I make a query with JOINS that returns the Mother's name and the Father's name? And knowing that the parents can have multiple children, and obviously a student can have two parents, Mother and Father...

I hope I explained it well, thank you beforehand

Answer 1

This is the simple way to do what you're asking:

(
    SELECT      KIDS1.id as student_id
                KIDS1.name as student_firstname
                KIDS1.lastname as student_lastname
                MOMS.id as parent_id
                MOMS.name as parent_firstname
                MOMS.lastname as parent_lastname
                MOMS.type as parent_type
    FROM        students KIDS1
    JOIN        parents MOMS ON KIDS1.motherid = MOMS.id
)
UNION
(
    SELECT      KIDS2.id as student_id
                KIDS2.name as student_firstname
                KIDS2.lastname as student_lastname
                DADS.id as parent_id
                DADS.name as parent_firstname
                DADS.lastname as parent_lastname
                DADS.type as parent_type
    FROM        students KIDS2
    JOIN        parents DADS ON KIDS2.fatherid = DADS.id
)

I've updated my query now so that you can get a list of the parents and what kind of parent it is (mother or father). This query will not list students that don't have a parent listed in your system. You will have to modify or write a different query to determine which students do not have a parent listed.