We've table1 with columns (id, firstname, lastname, gender).
Then table2 with columns (manid, bodyfat, mandate, matchid)
Then table3 with columns (womanid, bodyfat, womandate, wmatchid)
So, here i want to make an updatable mysql view with columns (id, firstname, lastname, gender) from table1, columns (bodyfat, mandate) from table2 and columns (bodyfat, womandate) from table3. The bodyfat is calculated through javascript and sended to database with php, so as you can understand in each row into the view either we take as null the columns of table2 (bodyfat, mandate) or else we take the columns of table3 as null (bodyfat, womandata) if we are talking about a man or a woman respectively.
Create view myview as
Select table.id, table1.firstname, table1.lastname, table1.gender,
table2.bodyfat, table2.mandate, table3.bodyfat, table3.womandate
From step1
JOIN table2 ON table1.id = table2.matchid
JOIN table3 ON table1.id = table3.wmatchid;
So if i use LEFT JOIN statement to built my view i will have the null values displayed properly but this view wont be updatable.
If else i use JOIN statement to built my view, the opossite thing happens so the view is now updatable but it can't keep null values.
Is there another join statement that i can use to do both keeping null values and also be updatable?