Mysql – Sort table so that linked rows show up as subsets in overall alpha sort

MySQLPHPsorting

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)

Best Answer

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.

Related Question