Fastest way to join mysql 4.0 data from multiple tables?

Question

Hi, I have 3 mysql 4.0 tables: all have fields ID(int), type(int) and another field, value which is either varchar(255), tinyint or int.

I need to write them all out and I end up getting three DataTables, looping over them, and creating rows into a temporary table (in .NET 1.1).

Do you see any faster/cleaner way than this to join or just write out this data?

Answer 1

I am not sure if you are wanting to actually join or display the results from all three tables in one query.

If you are just wanting flat out results, your best best would be to do a union such as:

SELECT 
    ID, 
    Type, 
    Convert(varchar(255), Value) as Value 
FROM 
    table1
UNION
SELECT 
    ID, 
    Type, 
    Convert(varchar(255), Value) as Value 
FROM 
    table2
UNION
SELECT 
    ID, 
    Type, 
    Convert(varchar(255), Value) as Value 
FROM 
    table3

Note: I am doing the convert so that you can get the most stable form (the varchar version) of all three of your fields.