joining 2 database tables in a single query instead of arraying it and making a mess

[Easter Bunny]

ok, i have to get two mysql tables to work as one. one is called sale_mast_data and the other is called sale_mast_data_h. they both have the same columns. the only difference is that the one with the "_h" has the history of previous shift sales. the two tables both contain sales and i want to take a look at what has been sold in total, not just what has been sold in the current shift or the old shifts.

i have tried various things like

SELECT * FROM Sale_Mast_Data_H S1, Sale_Mast_Data S2 GROUP BY S1.PluCode, S2.PluCode

but that joins the tables so that all the columns are duplicated.

SELECT S1.Description, S2.Description, SUM(S1.Quantity, S2.Quantity) FROM Sale_Mast_Data_H S1, Sale_Mast_Data S2 GROUP BY S1.PluCode, S2.PluCode

but this just tells me i have problem around the "SUM(S1... " area.

the only other thing i can think of doing is getting two ResultSets and then putting all the data into an array and then comparing the data in the arrays and adding the figures together that need to be added.

*headache*

is there an easy way?

[masijade]

This is an SQL question, but what the heck

SELECT Description, SUM(Quantity) FROM
  ((Select Description, Quantity, PluCode FROM Sale_Mast_Data)
   Union
   (Select Description, Quantity, PluCode FROM Sale_Mast_Data_H))
Group By PluCode

Union usually "ignores" duplicate rows. So, if a row in "_H" is an exact duplicate of a row in the other you will only get one copy of it. If you "need" both, use "UNION ALL".

[Easter Bunny]

thanks. sorry about being out of topic, that's why i added the bit about resultset and arrays so it would seem less out of place. :)

also doesn't look like many people roam the sql threads and i feel at home here at java.

thanks again.

ps: it tunes "each delivered table needs its own alias", but that's easily fixed by adding "NewSalesTable" before the "group by" clause.