Finding parents by their children's properties

Question

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:

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?

Could you use a subselect using your existing query? `select persons.email as parent_email where child_id in (select id from my_existing_query)` — GGGforce, Apr 01 '17 at 10:51
Sounds plausible, I just need to find a way to get to `child_id`...let my try... — Tim Pietzcker, Apr 01 '17 at 10:56
do you want only parents whose children are in section 4? or you need both in separete rows in your query? or in same rows like parent, email, children, email? — Vecchiasignora, Apr 01 '17 at 11:30
Yes, only those parents. At least that would be my first step - one query for the children, one for the parents. Ideally, I'd like to have their union - a single column of all e-mail addresses both of the children in section 4 and the parents of the children in section 4. — Tim Pietzcker, Apr 01 '17 at 11:33
It's a custom app that makes use of an MS Access database, yes. — Tim Pietzcker, Apr 01 '17 at 11:42
I don't understand the purpose of `COMPersonenRel1`; shouldn't table `COMPBereiche` reference directly `COMPersonen`? — Giorgos Altanis, Apr 01 '17 at 11:42
You're right, it works without that - will edit my question shortly. — Tim Pietzcker, Apr 01 '17 at 11:48
It is my impression that your first query is too complex, and as I started working on it I really cannot see the reason. — Giorgos Altanis, Apr 01 '17 at 11:49

score 1 · Answer 1 · answered Apr 01 '17 at 11:48

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())))

Thanks for helping me understand the problem better. I didn't get your solution to work, perhaps because of the quirks of MSSQL's syntax, but I appreciate your input very much. — Tim Pietzcker, Apr 01 '17 at 13:20

score 1 · Accepted Answer · edited Apr 01 '17 at 12:40

1

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

edited Apr 01 '17 at 12:40

Tim Pietzcker

328,213
58
503
561

answered Apr 01 '17 at 12:05

Giorgos Altanis

2,742
1
13
14

Test the field names, there might be a misspel somewhere. – Giorgos Altanis Apr 01 '17 at 12:20
Can it be that I spelled ElternTeil with capital T? You have it written as "Elternteil" – Giorgos Altanis Apr 01 '17 at 12:22
You have to break it to pieces then, start by a small `select * from COMPBereiche`; if it works (it will!) add the first two parts of the `where` clause (pay attention to the alias `b`, it must be introduced in the `from` clause), then start adding joins one by one. Let me know up to which point it works and when exactly it starts breaking down (I don't have MS Access installed, so this is the best I can do). – Giorgos Altanis Apr 01 '17 at 12:27
The error is most likely because microsoft access requires parentheses in the `from` clause when you have more than 1 join. http://stackoverflow.com/a/20929533/2333499 – SqlZim Apr 01 '17 at 12:27
Thank you @SqlZim, let's see if this is indeed the case. – Giorgos Altanis Apr 01 '17 at 12:28
Yes, it is. I'll edit the answer to show the query that worked. The only drawback remaining is that some e-mail addresses are duplicated when a parent has more than one child. – Tim Pietzcker Apr 01 '17 at 12:30
This can be solved easily with a `distinct`. Should you consider removing the `Bereich (Nr)` from the result set? – Giorgos Altanis Apr 01 '17 at 12:36
@TimPietzcker "duplicated when a parent has more than one child" **or** when a child belongs to more than one Bereich - or is this not possible? If it is, the `distinct` should be applied to the first query as well. – Giorgos Altanis Apr 01 '17 at 12:39
There can be only one Bereich at one moment in time. Adding `DISTINCTROW` to the first query solved the problem. Many thanks! – Tim Pietzcker Apr 01 '17 at 12:41
Kein Problem, mfG aus Athen! – Giorgos Altanis Apr 01 '17 at 12:42

Finding parents by their children's properties

2 Answers2