Good night all! I sit programming all day. Now at night I think badly). The task means is this: a. get a list of marriages in which 2 children were born.

In the table of peoples data is stored about the parents. The marriages table stores data on contracted and terminated marriages. And in the last table, which concerns this task, children are kept. The structure is as follows:

alt text

I drew it in the ax, because the table Peoples for mother and father was duplicated. I am writing a request according to the task (Conclusion of marriages and the names of parents who had 2 children during the marriage):

SELECT m.ID_marriage, m.Date_begin_marriage, m.Date_end_marriage, a.Fam_people, a.Name_people, a.SecondName_people, b.Fam_people, b.Name_people, b.SecondName_people FROM Marriages AS m, Peoples AS a, Peoples AS b WHERE ((b.ID_people = m.Code_wife) AND (a.ID_people = m.Code_husband) AND 2 = (Select COUNT(*) FROM Childrens c WHERE ((c.Code_father = a.ID_people ) AND (c.Code_mother = b.ID_people) AND (c.Date_born > m.Date_begin_marriage) AND (c.Date_born < m.Date_begin_marriage))));

The request does not display anything. Although I had to bring one family, which was born in marriage 2nd. I forgot to write nested SQL queries, so I could be wrong. Who knows how to? I will be grateful.

  • one
    Check here AND (c.Date_born> m.Date_begin_marriage) AND (c.Date_born <m.Date_begin_marriage) - hackNslash
  • In the end, probably should be and (c.Date_born <m.Date_end_marriage). That is, the last condition - the date of birth is less than the date of divorce, and not the conclusion as in your request. - ave-student
  • Yes, thanks everyone. Indeed, the request should look like this: SELECT m.ID_marriage, m.Date_begin_marriage, m.Date_end_marriage a, Peoples AS b WHERE ((b.ID_people = m.Code_wife) AND (a.ID_people = m.Code_husband) AND 2 = (Select COUNT (*) FROM Childrens WHERE ((c.Code_father = a.ID_people) AND (c.Code_mother = b.ID_people) AND (c.Date_born> m.Date_begin_marriage) AND (c.Date_born <m.Date_end_marriage)))); - IntegralAL
  • 1. Why do you need two identical Peoples tables? 2. Children should not be associated with Peoples, but with Maryage, if, of course, there are no illegitimate children. - BuilderC
  • @BuilderC, these are not identical tables, but the same one. In an ax when you build links, if the second table is associated with the first in two fields, then the image is duplicated, for clarity, nothing more. In this case, Childrens depends on the Code_father and Code_mother fields on the Peoples table for the ID_People field. Bastards are there. - IntegralAL

0