Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unanswered: A little help, please?

    I have three tables:

    families
    ----------
    fam_id
    lname

    individuals
    -------------
    fam_id
    ind_id
    fname
    membership_type_id

    membership_types
    ------------------
    membership_type_id
    male (boolean)
    adult (boolean)
    attends (boolean)

    Every individual is associated with a family and a membership type. The membership type dictates whether they are an adult, whether they are male or female and whether they are currently attending.

    What I need to do is, with a SQL statement (I'm using mySQL), get a list of all of the families, with the first names of all of the adults that are currently attending. In the case of a single person, this is one first name. In the case of a married person, it is two. For example:

    Jones, Martin
    Robinson, Susan
    Smith, Steve and Tracy

    I have this SQL statement which properly retrieves all of the married couples:

    SELECT f.fam_id,f.lname,CONCAT(i1.fname,' & ',i2.fname) AS fname,f.address,f.city,f.state,f.zip,f.phone
    FROM families AS f
    INNER JOIN individuals AS i1 ON f.fam_id=i1.fam_id
    INNER JOIN membership_types AS mt1 ON i1.membership_type_id=mt1.membership_type_id
    INNER JOIN individuals AS i2 ON f.fam_id=i2.fam_id
    INNER JOIN membership_types AS mt2 ON i2.membership_type_id=mt2.membership_type_id
    WHERE i1.male=1 AND mt1.child=0 AND mt1.attends=1
    AND i2.male=0 AND mt2.child=0 AND mt2.attends=1
    ORDER BY f.lname,fname


    I also have this SQL statement which properly retrieves all women (it would work for men too) but includes the married women

    SELECT f.fam_id,f.lname,i.fname,f.address,f.city,f.state, f.zip,f.phone
    FROM families AS f, individuals AS i, membership_types AS mt
    WHERE f.fam_id=i.fam_id AND i.membership_type_id=mt.membership_type_id
    AND i.male=0 AND mt.child=0 AND mt.attends=1
    ORDER BY f.lname,i.fname


    I have tried a subquery on the above SQL to try to retrieve all women that are not associated with a fam_id that has an adult man in it:

    SELECT f.fam_id,f.lname,i.fname,f.address,f.city,f.state, f.zip,f.phone
    FROM families AS f, individuals AS i, membership_types AS mt
    WHERE f.fam_id=i.fam_id AND i.membership_type_id=mt.membership_type_id
    AND i.male=0 AND mt.child=0 AND mt.attends=1
    AND f.fam_id NOT IN
    (SELECT mf.fam_id
    FROM families AS mf, individuals AS mi, membership_types AS mmt
    WHERE mf.fam_id=mi.fam_id AND mi.membership_type_id=mmt.membership_type_id
    AND mi.male=1 AND mmt.child=0 AND mmt.attends=1)
    ORDER BY f.lname,i.fname


    but this hangs the server up too long and I get an error that it took more than 30 seconds so it gave up.

    Anyone have any ideas on how to do this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you deal with a family that has three or more adults in it? What if there are just two males (father and son, two brothers, or other combinations) or just two females (mother and daughter, etc)? How do you deal with family members with two different surnames (such as married doctors, or adult children returning to the family after divorce, etc)?

    I think you really need to revisit this design. I don't think it will survive long in the real world. These design problems contribute to the problems you are having now.

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    3
    My problem is that there is information specific to families (such as address, anniversary, etc.) and information specific to individuals (first name, activities they are involved in, etc.). No family has 3 or more adults in it. If it is a father and son, it is an adult male and then a child (noted in the membership type table). Different surnames have been a problem as you suggest.

    If I only had an invididuals table, how would I group the individuals into families? For example, I want to mail a letter to all families that live in Texas. Getting all of the individuals that live in Texas is easy, but I don't want to send John Smith one letter and his wife Mary Smith another copy of the same letter. I want to send a letter "Dear John and Mary".

    Similarly, we are a church and we track who is here every Sunday. This is done family by family ("The Smith family was here", "The Jones family was not here"). How would we track family-related items if there is no family grouping (table)?

    I guess I am open at this point to ridding myself of that dreaded family table, but I will need help with solutions to the good things it provides.

    Thoughts?
    Last edited by dbshack; 05-18-04 at 11:51.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you represent a family where the father is 84, the son is 56, and the grandson is 30. The father is widowed, the son is married to a doctor with a different name, and the grandson is never married with still another name from his mother's first marriage. All four live at one address, consider themselves a family, and all are full members of the church? Just FYI, this isn't contrived, we actually have a family like that!

    For determining a family presence, would one inidividual be enough, would a majority consititute presense, or would all of them need to attend for the family to be present.

    Things get really complicated when you try to define what constitutes a family. The way that I solved this problem is to have a designated "head of household" (in the example I gave above, the son) that is stored as part of the family row.

    We track everything by individual, then roll it up to report by family when necessary. We typically address things to the head of household "and family", although I've also got a function that can build a list of names (with or without the name of the head of household as we need it at the moment).

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    3
    I see what you are saying. We do not track like that. "Family" to us means "immediate nuclear family", or you could say "household". In your sample case, each generation is its own family (except a minor, who is considered a child in the family of his or her parent). When the child goes to college, they become their own family. I am happy to (for example) send a letter addressed to the adults (parents), the adult children and the "grandparent(s)", even if they all live at the same address. I really want to avoid the "and family." Being a church, we want to keep things very personal.

    One avenue I haven't tried is setting up temporary tables and filling it upon a given request. Cumbersome, yes. But maybe the best method to accomplish it all.

    Any further thoughts are most welcome. I appreciate your time.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As I don't have your data, or even know your database type for sure (although I'd bet it is MySQL), I'd suggest that you try:
    Code:
    SELECT  f.fam_id, f.lname, CONCAT(i1.fname
    ,  Coalesce(Concat(' & ', i2.fname), '')) AS fname
    ,  f.address, f.city, f.state
    ,  f.zip, f.phone 
       FROM individuals AS i1
       INNER JOIN membership_types AS mt1
          ON mt1.membership_type_id = i1.membership_type_id
       LEFT OUTER JOIN families AS f
          ON f.fam_id = i1.fam_id 
       LEFT OUTER JOIN individuals AS i2
          ON i2.fam_id = f.fam_id
       LEFT OUTER JOIN membership_types AS mt2
          ON mt2.membership_type_id = i2.membership_type_id
       WHERE (mt1.male = 1 AND mt1.child = 0 AND mt1.attends = 1
          AND mt2.male = 0 AND mt2.child = 0 AND mt2.attends = 1) 
          OR (i2.fam_id IS NULL and 1 = mt1.adult AND mt1.attends = 1)
       ORDER BY f.lname,fname
    -PatP
    Last edited by Pat Phelan; 05-18-04 at 16:30.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •