[SOLVED] mysql union used as a table for a join ??

[funkyres]

Here is my union -

 

select hexagon,rec_date,(1) from museum where herpid=1 UNION select hexagon,rec_date,(0) from records where herpid=1 AND verified=1;

 

The results -

 

+---------+------------+---+
| hexagon | rec_date   | 1 |
+---------+------------+---+
|     524 | 1926-08-24 | 1 | 
|     773 | 2003-06-05 | 1 | 
|    1822 | 2003-06-25 | 1 | 
|     646 | 2003-07-18 | 1 | 
|     881 | 2009-01-22 | 0 | 
+---------+------------+---+

 

I want to treat those results as a table for another query.

IE - if the result could be treated as a virtual table named 'hexrecords' and there was another table (a real table) called 'hexdata' that had columns for hexagon,lon,lat - I would want to be able to do this:

 

SELECT hexagon,rec_date,1,lon,lat FROM hexrecords,hexdata WHERE hexdata.hexagon = hexrecords.hexagon ORDER BY rec_date DESC

 

What I want to do works if hexrecords is a real table; but it's not, it's a union of specific data from other tables.

 

-=-

Also, is there a trick to getting the column name that shows as 1 to be called something else? I suppose it doesn't matter that much.

Is that possible?

[funkyres]

I solved it by doing two joins and then doing a union of the two joins.

As the the second question, yes - (0) AS foo