This new method selects the names as you would normally, joining only once to the names table. But then add a GROUP BY clause to the query, grouping by the common factor for the family (NANames.FamNu for Shelby v5). After you group the results, you can use a CASE statement to select either the Head of House or the Spouse name to appear in the results.
In the example query below I have selected all the adult choir members by using an inner join to the NAProfiles table and at the same time limiting the included Profile values to those that begin with MUAC, which is a common prefix on all the adult choir profile codes. The query uses GROUP BY and CASE statements to pull the spouses together and list them in the appropriate columns of the results.
HHFirstName = max(case when Names.UnitNu = 0 then Names.FirstMiddle end),
HHLastName = max(case when Names.UnitNu = 0 then Names.LastName end),
SPFirstName = max(case when Names.UnitNu = 1 then Names.FirstMiddle end),
SPLastName = max(case when Names.UnitNu = 1 then Names.LastName end)
Shelby.NANames as Names inner join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter and Profiles.Profile like 'MUAC%'
Here are the results:
You can see that the Blackwells , the Carters, and the Glovers are in the choir together. In the visible results all the other individuals do not have a spouse in the choir.
With the two techniques from last week and this week, you should be able to get the names of spouses together whenever you need them in one row of results. Of course, listing them on one row is usually only part of the requirement. Often you will want to combine them together into one column as well, with a result such as "John & Jane Doe" or "Mr. & Mrs. Smith." That process is surprisingly complex, and will be the topic for next week's post.