Fastest way to join mysql 4.0 data from multiple tables?

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?

From stackoverflow

• 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.

  Spikolynn : Thanks, actually it worked even without converting, but i'm worried because instead of 420 results I'm only getting 410 this way - maybe some are duplicates and are merged?

  TheTXI : @Spikolynn: it may be possible that the an implicit conversion is occuring in your ints and tiny ints (or your varchars are trying to convert to ints) and when it fails conversion it is getting dropped from the result set.

  Spikolynn : TheTXI: I tried with Convert(Value, char) instead of Value all three times, but it still shows not enough results. I don't mind much though, because the app should work the same if they are just duplicates as I presume.

  Spikolynn : yup, i checked for duplicates today and they were exactly as many as the missed records count. maybe i should add a unique constraint over the three fields

  Spikolynn : oh, and I had to use convert, because otherwise I would get an .NET exception about incompatible column types :S