Query Problem with mysql

[barianto]

Hi,

I'm using mysql as database and VB as programming language. I have four table which is buyer2005, buyer2006, good2005 and good2006, the last four digit represent year.

My question is how to make SELECT subquery or join in MySql which second table name come from one field on first Table?
Example:
SELECT T1.GoodCode, T1.GoodName, T1.BuyerCode, T1.DatePurcashed, T2.BuyerName FROM good2005 AS T1 LEFT JOIN buyer--MID(T1.DatePurcashed,1,4)--

How to do that?
I just migrate from Foxpro whom is give me possibility to do such thing. If there is no other i will redesign my database.

Thank you

[Mark Gambo]

I don't quite understand what you are looking to do. Could you post some sample data for each of the tables and how you want the data returned from the query. Secondly, if I were you I would normalize that database and do away with year specific tables (buyer2005, buyer2006, good2005 etc.) I would create a new table based upon the schema of your tables and I would add a new field that will be used to hold Year value. THis seems to me to be a better approach to your problem.

[barianto]

Sorry, right now i can't give you sample data. And after "deep" thinking, yes i will normalize the database.

Thanks Mark