mysql - Display the values of 2 foreign keys -

i creating mysql database has relation in form of:

what trying achieve create view displays details of both couples @ once, example column names be:

|| p1_first_name || p1_lastname || p1_gender || p2_firstname || p2_lastname || p2_gender || 

however not sure how this. far have along lines of

create view coupledetails select people.`first name`, people.`last name`, people.`gender` couples left join people on people.ni_number = couples.ni_person1; 

which works fine getting details of first person. i've spent last few hours trying create table displays columns need cannot work described above - , quite frankly i'm not sure i'm meant doing! please provide me guidance in correct direction!

you need join table people twice against table couples since there 2 columns of table couples depended on table people.

i think columns on table couples required (or non-nullable) because cannot called couple when there 1 person :)

create view coupledetails  select  b.firstname p1_firstname,         b.lastname p1_lastname,         b.gender = p1_gender,         c.firstname p2_firstname,         c.lastname p2_lastname,         c.gender = p2_gender    couples         inner join people b             on a.ni_person1 = b.ni_number         inner join people c             on a.ni_person2 = c.ni_number 

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins