Can I make an SQL query for two rows in each row?

Question

I have an unusual SQL table (not mine) that has the following fields (among others): last_name, primary_name, secondary_name denoting married couples. It is assumed that the surname is common (not very modern, I know), and if it is not a pair, then either primary_name or secondary_name can be NULL. (There are also some duplicates in the table.)

What I want to do is get a list of all the names ("first last") in the database, in alphabetical order in the usual order. Now I am 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 is not in alphabetical order, because the second pass begins again.

How can I get all the names at once, in alphabetical order completely? Is there a way to do this in SQL, or do I need to build two arrays and regroup 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 result will be something like this:

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

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

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

Answer 1

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 (unlike UNION ALL ) will duplicate the results.

Another is to create a join in the 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 

Answer 2

If I understood 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 looks like in some cases you have one line for last_name where both primary_name and secondary_name are populated. The following query should give you the desired result (sorry, this time not COALESCE):

 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 3

I think you need:

 SELECT primary_name AS name , last_name FROM members WHERE primary_name IS NOT NULL UNION SELECT secondary_name , last_name FROM members WHERE secondary_name IS NOT NULL ORDER BY last_name, name 

Another rewriting code uses UNION ALL :

 SELECT COALESCE(primary_name, secondary_name) AS name , last_name FROM members UNION ALL SELECT secondary_name , last_name FROM members WHERE primary_name IS NOT NULL AND secondary_name IS NOT NULL ORDER BY last_name, name 

The second version may be faster, but it may show duplicate results. If, for example, you have these lines, one Joe Jackson married to Susan , and one married Lea :

 last_name primary_name secondary_name ---------------------------------------- Jackson Joe Susan Jackson Joe Lea 

The first qould query shows:

 name last_name ----------------- Joe Jackson Lea Jackson Susan Jackson 

and the second will have "duplicates":

 name last_name ----------------- Joe Jackson Joe Jackson Lea Jackson Susan Jackson 

Which is more correct depends on your specifications.