CODEWITHSUNDEEP
sqlsql-serverms-access
Tim Pietzcker
Tim Pietzcker

Reputation: 336168

Finding parents by their children's properties

I have a database containing information about members of a choir (which is divided into sections) and their parents. I need to build an e-mail list containing all children in a specific section of the choir as well as their parents.

There is a large table COMPersonen which contains all the personal information I need (especially their ID number Nummer and primary e-mail address Serienbrief E-Mail Adresse).

Another table COMPBereiche contains all the details about which section(s) (Bereich (Nr)) of the choir a certain person belonged to at a certain time. It is linked to the primary table via Nummer/Person (Nr). The [Bis] (= "until") field can be used to determine whether a person is currently a section member by checking if that field is either empty or set to a future date.

Lastly (at least for this purpose) there is a table COMElternKind that contains the ID number of all the parents (Elternteil (Nr)) and the ID numbers of their children (Kind (Nr)). Of course, each parent can have more than one child and vice versa.

So far, this is pretty straightforward. In Access, the relations look like this:

Database relations

The easy part was building a query that returns all the addresses for the children in a specific section of the choir (for example the section with the ID Bereich (Nr) = 4):

SELECT COMPersonen.Nummer, COMPersonen.[Serienbrief E-Mail Adresse],
       COMPBereiche.[Bereich (Nr)], COMPBereiche.Bis
FROM COMPersonen 
INNER JOIN COMPBereiche ON COMPersonen.[Nummer] = COMPBereiche.[Person (Nr)]
WHERE (((COMPersonen.[Serienbrief E-Mail Adresse]) Is Not Null) 
AND ((COMPBereiche.[Bereich (Nr)])=4) 
AND ((COMPBereiche.[Bis]) Is Null Or (COMPBereiche.[Bis])>=Date()));

But now I also need the addresses of the parents whose children are in section 4 of the choir at the moment. How would I go about that? Finally, the two SELECT statements would need to be combined, but my guess is that that's a simple UNION, right?

Upvotes: 1

Views: 164

Answers (2)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Do you think that this is too simplistic?

First query (children):

SELECT p.*, b.[Bereich (Nr)]
FROM COMPBereiche b INNER JOIN COMPersonen p ON b.[Person (Nr)] = p.Nummer 
WHERE b.[Bereich (Nr)] = 1001 
AND (b.Bis IS NULL OR b.Bis >= '2016-01-01')
and p.[Serienbrief E-Mail Adresse] IS NOT NULL

Second query (parents):

SELECT DISTINCTROW e.*
FROM ((COMPBereiche b INNER JOIN COMPersonen p ON b.[Person (Nr)] = p.Nummer) 
INNER JOIN COMElternKind k ON p.Nummer = k.[Kind (Nr)])
INNER JOIN COMPersonen e ON e.Nummer = k.[Elternteil (Nr)]
WHERE b.[Bereich (Nr)] = 1001 
AND (b.Bis IS NULL OR b.Bis >= '2016-01-01')
AND e.[Serienbrief E-Mail Adresse] IS NOT NULL

Upvotes: 1

Vecchiasignora
Vecchiasignora

Reputation: 1315

every parent+children whose have children in your logic, rowcount will be equal children count 

select prnt.[Nummer] as parentnum, prnt.[Serienbrief E-Mail Adresse] as parentemail,
           prnt.[Bereich (Nr)] parentBereinch, prnt.[Bis] parentBis, chld.*
           from COMPersonen prnt 
  INNER JOIN COMelternKind cmk, ON prnt.[Person (Nr)] = cmk.[Elternteil (Nr)] 
  INNER JOIN
    (SELECT COMPersonen.Nummer as chldnum, COMPersonen.[Serienbrief E-Mail Adresse],
           COMPBereiche.[Bereich (Nr)], COMPBereiche.Bis
    FROM COMPersonen 
    INNER JOIN (COMPersonenRel1 
        INNER JOIN COMPBereiche 
        ON COMPersonenRel1.[Person (Nr)] = COMPBereiche.[Person (Nr)]) 
    ON COMPersonen.[Nummer] = COMPersonenRel1.[Person (Nr)]
    WHERE (((COMPersonen.[Serienbrief E-Mail Adresse]) Is Not Null) 
    AND ((COMPBereiche.[Bereich (Nr)])=4) 
    AND ((COMPBereiche.[Bis]) Is Null Or (COMPBereiche.[Bis])>=Date()))) chld
ON cmk.[Kind (Nr)]) = chld.[chldnum]

every parent whose have children in your logic, rowcount will be only parents count

select * from COMPersonen prnt 
  INNER JOIN COMelternKind cmk, ON prnt.[Person (Nr)] = cmk.[Elternteil (Nr)]

  where exists (SELECT COMPersonen.Nummer as chldnum, COMPersonen.[Serienbrief E-Mail Adresse],
           COMPBereiche.[Bereich (Nr)], COMPBereiche.Bis
    FROM COMPersonen 
    INNER JOIN (COMPersonenRel1 
        INNER JOIN COMPBereiche 
        ON COMPersonenRel1.[Person (Nr)] = COMPBereiche.[Person (Nr)]) 
    ON COMPersonen.[Nummer] = COMPersonenRel1.[Person (Nr)]
    WHERE ((COMPersonen.Nummer = cmk.[Kind (Nr)])  
    AND ((COMPersonen.[Serienbrief E-Mail Adresse]) Is Not Null) 
    AND ((COMPBereiche.[Bereich (Nr)])=4) 
    AND ((COMPBereiche.[Bis]) Is Null Or (COMPBereiche.[Bis])>=Date())))

Upvotes: 1

Related Questions