MySql Self Join Count -

MySql Self Join Count -

i have question concerning mysql query. there 2 tables, persons , married.

persons: id surname firstname id_father id_mother married: id personid1 personid2 marriage divorce

personid1 male , id2 female.

my goal getting list containing firstname, surname , number of kids of women or 1 time married person id = 1. somehow can't set in 1 select statement.

here 2 statements i've been using far.

select firstname, surname, id persons id in( select married.personid2 married married.personid1 = 1);

select persons.id_mother id, count(persons.id_mother) noofchildren persons grouping persons.id_mother;

thanks in advance

http://www.directupload.net/file/d/3661/vwqxeg8a_png.htm

i think query job.

select woman.firstname, woman.surname, count(kid.id) kids persons woman inner bring together married on married.personid2 = woman.id left bring together persons kid on kid.id_mother = woman.id married.personid1 = 1 grouping woman.id

mysql count group-by self-join