Joining multiple tables

[aspr1n]

I have 3 MySQL tables that all have the fields ID, and NAME with identical field types.

I want to join those two fields from all 3 tables into one large one. I know this isn't strictly a join but certainly in MySQL front it won't let me merge fields with the same name.

Any thoughts would be very much appreciated.

asp

[WebJoe]

have you tried UNION?:

INSERT INTO DESTINATION_TABLE (ID, NAME) SELECT SRC_TABLE1.ID, SRC_TABLE1.NAME FROM SRC_TABLE1 UNION SELECT
SRC_TABLE2.ID, SRC_TABLE2.NAME FROM SRC_TABLE2 UNION SELECT
SRC_TABLE3.ID, SRC_TABLE3.NAME FROM SRC_TABLE3;

[aspr1n]

WebJoe,

You're a gem, I thought UNION was only possible in 4.x MySQL, however a little search revealed this for achieving it in 3.x:

www.nstep.net/~mpbailey/programming/tutorials.union.php

Cheers,

asp

[edited by: jatar_k at 4:53 pm (utc) on Mar. 16, 2003]
[edit reason] delinked [/edit]

[WebJoe]

You're welcome. I didn't even know that UNION is not supported in certain DBMS', since it is part of the SQL-2 specification (at least its 1992 draft)...

Anyway, I'm glad I could help.