1

I have this member table where memberships are either single or family, and if family they can be family head or dependent. When outputting a view of the membership, it currently sorts alpha ascending, but I'd like to find a way to put all of the dependents of a family head below the head.

The relevant columns are:

isFam ( tinyint(1) )
isFamHead ( tinyint(1) )
memKey ( int(11) )

Is there some way, in a mysql query, to sort the table alphabetically for single members and family heads, but put the dependents of the family head directly beneath the family head?

(This sort of works right now if all family members have the same last name, but in many cases I find they don't. And different families can have the same last name as well, so ...)

From the comments:

  • memKey identifies a membership. For a single membership, there will be only one row with that memKey; for a family membership, all members of a given family will have the same memKey.
  • For a single membership, isFam is 0; for all members of family memberships, isFam = 1.
  • The "family head" of each family membership has isFamHead = 1; there should only be one row where isFamHead = 1 per memKey. (Ignore value of isFamHead for single members)

2 Answers 2

1

So - making the following assumptions:

  • memKey is the same for a family head and all subsidiary memberships for the family;
  • There is no direct pointer to the family head record in a subsidiary member row.
  • isFamHead acts as a Boolean, where "1" means the row represents a family head, and "0" means it doesn't.
  • There is only one row for any given memKey where isFamHead = 1

From the comments: Members with "single" memberships (1 person, no family) will have isFam = 0. Members who are part of a family membership will have isFam = 1; only the primary member from the family will have isFamHead = 1, it will be 0 for the rest.

Something like this should work. Column and table names are made up, of course, since none were provided except for the three relevant columns.

SELECT CASE WHEN `isFamHead` = 1 OR `isFam` = 0 THEN CONCAT(lastname,', ',firstname) ELSE '' END
       as FamilyHead
      ,CASE WHEN `isFamHead` = 0 AND `isFam` = 1 THEN '' ELSE CONCAT(lastname,', ',firstname) END
       as FamilyMember
      ,'blah blah' as Additional_Columns
  FROM (
        SELECT fh.lastname as fh_lastname
              ,fh.firstname as fh_firstname
              ,m.*
          FROM member m
                 INNER JOIN member fh ON (m.memKey = fh.memKey AND fh.isFamHead = 1)
       ) sq
 ORDER BY fh_lastname, fh_firstname, memKey, isFamHead DESC, lastname, firstname;

Our subquery adds the first and last name of the isFamHead row for the current row's memKey to the current row's data.

Then, we sort by: - the family head's last and first names - memKey (just in case there are two family heads with the same name - this pulls each actual family group together) - isFamHead in descending order - this makes the family head the first row of each family group - finally, the members' last and first names.

If we have the following families:

  • John Smith (head), Jane Levy, Paul Smith-Levy, Frank Smith (memKey = 10000)
  • John Smith (head), Patty Smith, Jack Smith, Alvin C Monk (memKey = 10001)
  • Adrian Monk (head), Trudy Monk, Ambrose Monk
  • Steve Rogers (a single member)

This should give us the following output:

Family Head          Family Member
------------------   -------------------
Monk, Adrian
                     Monk, Ambrose
                     Monk, Trudy
Rogers, Steve
Smith, John
                     Levy, Jane
                     Smith, Frank
                     Smith-Levy, Paul
Smith, John
                     Monk, Alvin C
                     Smith, Jack
                     Smith, Patty

NOTE: code is untested.

4
  • In the second CASE statement, should it not be isFamHead = 0 AND isFam = 1?
    – Robusto
    Jun 27, 2017 at 19:33
  • I assumed that every memKey would have one row where isFamHead = 1; that would be the family head for a family membership, and the member for a single membership. Updated to add assumption. If you'll clarify what the four possible settings for isFam and isFamHead would mean, I'll update to accommodate.
    – RDFozz
    Jun 27, 2017 at 19:45
  • Also - note that the second CASE outputs an empty string when the WHEN statement is true (i.e., this rows is a family head), and a name when it's false (not a family head).
    – RDFozz
    Jun 27, 2017 at 19:49
  • Thanks. Taking those with the same memKey as comprising a family group, all will have isFam = 1 true, but only family heads will have isFamHead = 1 true. Single memberships will have isFam = 0 true..
    – Robusto
    Jun 27, 2017 at 20:58
0

You should consider some group ID within this or another relataed table. Marking accounts with the same group ID you can have 1 or more of them as an enhanced account type.

Make sense?

1
  • The memKey column is the group ID.
    – Robusto
    Jun 27, 2017 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.