Listing Husband and Wife on One Line - Part 1

There are several ways to combine a husband and wife in query results, each one offering an advantage over the others depending on the overall design of the query. The first method I want to throw out there assumes that the Head of House will be the "primary" name chosen by the WHERE condition of the query. Adding the spouse involves joining a second time to the names table (NANames for Shelby Systems v.5).

Before examining the query statement itself, here are the noteworthy columns of the NANames table when it comes to combing family members together:

• NameCounter - a numeric value that is unique for every person in the table
• FamNu - a numeric value shared by all members of the same family; it is helpful to realize that this value is the same as the head of household's NameCounter value.
• UnitNu - a numeric value indicating the family position in the household; the head of house value is zero (0) and the spouse value is one (1).

The following query lists all the heads of house in the NANames table and lists the spouses' names along side.


select
HeadOfHouse = HH.FirstMiddle + ' ' + HH.LastName,
Spouse = SP.FirstMiddle + ' ' + SP.LastName
from
Shelby.NANames as HH left join
Shelby.NANames as SP on HH.FamNu = SP.FamNu and SP.UnitNu = 1
where
HH.UnitNu = 0 and HH.TitleCounter > -1


The UnitNu condition in the WHERE clause is there to prevent the spouses and children from being listed on a separate line. The TitleCounter condition is there to eliminate businesses and other organizations from the list.

A simple variation of this approach allows you to add the spouse of either husband or wife, depending on who is included by the WHERE criteria. In the following example the WHERE condition is not given, but the FROM condition will add the spouse of whichever name is part of the result list.


select
HeadOfHouse = ChosenNames.FirstMiddle + ' ' + ChosenNames.LastName,
Spouse = Spouses.FirstMiddle + ' ' + Spouses.LastName
from
Shelby.NANames as ChosenNames left join
Shelby.NANames as Spouses on ChosenNames.FamNu = Spouses.FamNu and Spouses.UnitNu = ABS(ChosenNames.UnitNu - 1)
where
any conditional statements


The join to the "Spouses" instance of NANames uses a mathematical operation to return a 1 whenever the primary name has a UnitNu value of 0 and to return a 0 whenever the primary name has a UnitNu value of 1. Thus the spouse will be added to any head of house, and the head of house will be added to any spouse.

Both variations of this approach use a LEFT join to add the spouse information. This type of join means that all the primary names will be listed, regardless of whether there actually is a spouse in the system for that person or not.