i have three tables (Families) , (Children) and (Children_Payments) i want to insert payment to each child that his father has state A or B .. the problem is not every father has children and sql server told me "you cannot insert to null children_id
value"
what is the solution .. my sql statment to insert payment is :
insert into ChildrenPayments
(payment_value , payment_class , children_id) select @payment_value , @payment_class , fc.children_id
from (select f.family_id , c.children_id FROM
(SELECT family_id, father_state FROM dbo.Families WHERE
(father_state = 'A') or father_state = 'B'))f
left outer join dbo.Children AS c ON
c.family_id = f.family_id AND c.children_state <> 'dead' AND c.children_state <> 'absence')fc