Can I make an SQL query do two matches on every row?

Question

I have an unusual SQL table (not mine) which has the following fields (among others): last_name, primary_name, secondary_name, denoting married couples. The last name is assumed to be shared (not very modern, I know), and if it's not a couple, then either the primary_name or secondary_name may be NULL. (The table also has several duplicates.)

What I want to do is get a list of all names ("first last") in the database, alphabetized in the usual manner. Right now I'm doing two passes through the database using PHP and PDO:

$qstr = "SELECT DISTINCT primary_name, last_name 
            FROM members 
            WHERE primary_name IS NOT null
            ORDER BY last_name, primary_name";
$sth = $dbh->prepare($qstr);
$sth->execute();
// output the results

$qstr = "SELECT DISTINCT secondary_name, last_name 
            FROM members 
            WHERE secondary_name IS NOT null
            ORDER BY last_name, secondary_name";
$sth = $dbh->prepare($qstr);
$sth->execute();
// output the new results

But the end result isn't alphabetized because the second pass starts over again.

How can I get all the names at once, alphabetized completely? Is there a way to do this in SQL, or do I need to build two arrays and re-alphabetize them in PHP afterwards?

EDIT The database looks something like this:

last_name  primary_name   secondary_name
----------------------------------------
Abrams     Joe            Susan
Miller     Sam            Abby

The desired output would be something like this:

["Joe Abrams","Susan Abrams","Abby Miller","Sam Miller"]

Instead, if the first pass gets all the husbands and the second pass all the wives, I'm getting something like this:

["Joe Abrams","Sam Miller","Susan Abrams","Abby Miller"]

Answer 1

If I understand correctly, I think you are looking for something like this:

select distinct coalesce(primary_name, secondary_name) as pri_sec_name,
    last_name
from members
where coalesce(primary_name, secondary_name) is not null
order by last_name,
    coalesce(primary_name, secondary_name)

Update

It sounds like in some cases you have one row for a last_name, where both primary_name and secondary_name are populated. The query below should give you the output you want (sorry, no COALESCE this time):

select last_name, pri_sec_name
from (
    select primary_name as pri_sec_name, last_name from members where primary_name is not null
    union all
    select secondary_name as pri_sec_name, last_name from members where secondary_name is not null
) a 
order by last_name, pri_sec_name

Answer 2

An alternative is to use UNION...

SELECT
  *
FROM
(
  SELECT primary_name AS pri_sec_name, last_name 
    FROM members 
   WHERE primary_name IS NOT null

  UNION

  SELECT secondary_name AS pri_sec_name, last_name 
    FROM members 
   WHERE secondary_name IS NOT null
)
  AS data
ORDER BY
  last_name, pri_sec_name

NOTE: UNION (as opposed to UNION ALL) will de-duplicate the results.

Another is to do a join on a mapping table.

SELECT
  members.last_name,
  CASE WHEN map.mode = 1 THEN members.primary_name ELSE members.secondary_name END AS pri_sec_name
FROM
  members
INNER JOIN
  (SELECT 1 as mode UNION ALL SELECT 2 as mode) AS map
    ON (map.mode = 1 AND members.primary_name   IS NOT NULL)
    OR (map.mode = 2 AND members.secondary_name IS NOT NULL)
ORDER BY
  1,
  2