SELECT table name

[Anon]

Hi,

I have several tables which contain the exact same structure but are split into different tables because they are of different class. For example, this is what I have:

sharp_orders
ricoh_orders
minolta_orders
etc.

sharp, ricoh, and minolta are on different domains and operate different sites, but their orders are stored in the exact same structure.

So I've created a central admin for all orders from these sites. This is the query I use to select all the orders:

SELECT sharp_orders., ricoh_orders., minolta_orders.* FROM sharp_orders, ricoh_orders, minolta_orders ORDER BY date_ordered DESC

The problem is that in the result set, I do not know which order belongs to which brand!

I can change the system to place all orders in one table, but that would take too much time :-)

So is there anyway I can SELECT an additional column that would give me the table name? example:

---

| table | order_id | date_added |

| sharp | 1 | 1000110101 |

| ricoh | 1 | 2323123131 |

so table is not really a field in the table, but a mysql generated column telling me what table it belongs to.

-sridhar

[gyoung]

You could try a UNION query:

select 'sharp' as 'table', order_id, date_added
from sharp_orders
union
select 'ricoh', order_id, date_added
from ricoh_orders
union
select 'minolta', order_id, date_added
from minolta_orders
order by date_added desc

[Anon]

I'm running this shopping site with a lot of other sites. I wanted to upgrade to mysql 4.0, but is that stable for deployment?

Will i experience any bugs or pitfalls? I'm mainly running stardard, simple queries involving select, update, insert, delete, etc. So nothing special :-)

-sridhar