based on your tables these rows would not need to be combined just pulled into one big list. Seeing how the field definitions are the same a union would do that. I created a table and ran this.

select c_id, cdate, cprice, crans_type from xoops.buyer
union
select c_id, cdate, cprice, crans_type from xoops.seller
;

results are 1 big table.

hope that helps, Gabriel

Gabriel,

Thanks for the reply. I don't have any problems using union to combine the lists, the problem I am having is to do "the final query" on the union. I have been searching for a way that I could manipulate the union table (for simplicity I call it union table ).

Based on my limited reading of MySQL manuals and google search, MySQL 4.1 onwards allows union subquery. But I would have to wait for the 4.2 version so my hosting company could upgrade.

For now a temp table is the only way to go.

could you post sample (fake) data to show us an example of how the data would differ between the tables?


from what i'm understanding, tableA and tableB handle totally different transactions, right?

Quote:

No, they are not that different from each other. Theoretically, they should be in the same table (would save me a lots of trouble). They are stored in tableA or tableB according to top-level attributes. Essential information in tableA and tableB are the same, but each table has its own unique fields. I am trying to combine the common fields and do an arithmetic calculation afterwards.

What I really want to do is a simple union query like following, which I can't do with MySQL 4.0 (I would have to wait for MySQL 4.2)

select year, type, count(*), sum(price), avg(price), max(price), min(price)
FROM
((select year(purchase_date) as year, purchase_price as price, transaction_type as type from tableA)
union
(select year(sale_date), sale_price, transaction_type from tableB))
GROUP by year, type