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