merge select from 2 table

[Bob Bedford]

I've 2 tables where I want to retrieve the id and name from and merge them:

table1:
IDtable1-nametable1
1 - 578445
2 - 454545
3 - 554887

table2:
IDtable2-nametable2
X1 - 544512
X2 - 445775

The result should look like (order by nametable)
X2 - 445775
2 - 454545
X1 - 544512
3 - 554887
1 - 578445

(this result will be used for filling a combobox).
I may excecute 2 queries, put the results in an array and then order it, but
I'm sure there is a better way in MySQL, but I couldn't find it.

Bob

[Colin McKinnon]

Bob Bedford wrote:

I've 2 tables where I want to retrieve the id and name from and merge
them:

<snip>

Depends on the version of MySQL. More recent ones will allow:

( SELECT id, value, 'a' as tableid from table1 )
UNION
( SELECT id, value, 'b' as tableid from table2 )
ORDER BY tableid

HTH

C.

[Steve]

I've 2 tables where I want to retrieve the id and name from and merge

them:

Alternatively, use a temporary table to simulate a UNION (if your
version of MySQL doesn't support it)...
CREATE TEMPORARY TABLE MyTempTable ( ID INT, Name VARCHAR(25) )

INSERT INTO MyTempTable ( ID, Name )
SELECT IDtable1, nametable1
FROM Table1

INSERT INTO MyTempTable ( ID, Name )
SELECT IDtable2, nametable2
FROM Table2

SELECT ID, Name
FROM MyTempTable
ORDER BY Name

---
Steve