Joining and sorting results of two tables in MySQL

Question

Hi,

I have two tables in MySQL, one containing a field City and one containing a field HomeCounty. I need to get X number of records sorted alphabetically so that both City and HomeCounty are taken into the set.

I can't join, because these two tables have no relation... and because I need these two columns to be "one", not two.

So, I want all City records and HomeCounty records to be in one set, then sort that set alphabetically and limit it to X. I have really no idea what to do. Union?

Answer 1

something similar to this:

select name from 
(
select city as name from table a
union 
select country from table b
)
order by name

Answer 2

Yes, you'd use a UNION:

SELECT city AS name
  FROM TABLE_1
UNION ALL
SELECT homecountry AS name
  FROM TABLE_2
ORDER BY name
   LIMIT ?

Change to UNION if you have duplicates you want to remove, but it will be slower than UNION ALL.

The ORDER BY in a UNION is applied to the entire resultset. If you wanted to apply different ORDER BY criteria to each statement in the UNION, you have to define the statement with brackets:

 (SELECT city AS name
    FROM TABLE_1
ORDER BY name DESC)
UNION ALL
SELECT homecountry AS name
  FROM TABLE_2
   LIMIT ?