How to get table name from SELECT?

I have four tables (let's call them small tables), and in each are four identically named fields (ID, LName, FName, EMail). There is also other information that varies between tables.

I want to combine some of the data from each of the small tables into one big table to allow searches on all individuals. But I also want to create links in the search return where users can access the richer, more detailed information in the underlying small table.

Strategy to date:
<OL TYPE=A>
<LI>if big table exists, drop it
<LI>if big table not exist, create it
<LI>query small table, using INSERT INTO SELECT to populate big table
<LI>repeat query for each small table
</OL>

Problem: have not figured out how to add the source small table name into what is INSERTed into big table. Can get the unique id from small table, but getting table name and putting it into big table has me stumped URL.

Would appreciate some advice...

Tom

Your database structure is not optimal.

You should create one table with all the products and common details. Let's say:
Prod_id, name, description, price. And for the specific details you make different tables (four different tables in you example). Let's say:
detailTable1: "Prod_id, 1detail1, 1detail2, 1detail3"
detailTable2: "Prod_id, 2detail1, 2detail3, 2detail3"
etc.

Mzzls
Dave

Thanks, Dave. I agree, my table structure is not optimal. We started with one table, and as we incorporated very different groups of people, we let their differences as groups influence us, resulting in creating tables that are not normalized. Should have normalized then -- and this is on my long-range set of tasks -- but no time right now.

In the meantime, the workaround I would prefer would be a UNION of the tables, but that is not supported in my version of MySQL. Any other options or ideas will be much appreciated.

Tom