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

mysql, pdo, php, sql

Solution

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

Problem

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"] ```

Original source