MySQL Server Forum - Table join or union?

Author

Table join or union?

callista

2006-03-13, 9:23 am

Hi,

I have two tables.

Table 1 (Date, Total)
Table 2 (Date, Total)

I'm trying to find a select statement which will give me all the dates
from Table 1 and Table 2 in a column along with Table1.Total and
Table2.Total.
ie.
Dates, Table1.Total, Table2.Total

I'm told that I would need a FULL OUTER JOIN to be able to do this but
according to the MySQL reference, FULL OUTER JOIN is not supported? How
would I be able to achieve the required result?

noone

2006-03-13, 1:23 pm

callista wrote:

> Hi,

> I have two tables.

> Table 1 (Date, Total)
> Table 2 (Date, Total)

> I'm trying to find a select statement which will give me all the dates
> from Table 1 and Table 2 in a column along with Table1.Total and
> Table2.Total.
> ie.
> Dates, Table1.Total, Table2.Total

> I'm told that I would need a FULL OUTER JOIN to be able to do this but
> according to the MySQL reference, FULL OUTER JOIN is not supported? How
> would I be able to achieve the required result?

Are these the only 2 fields in each table?? not very descriptive if you
ask me...

Is DATE unique in each table (only 1 entry per day(date))?

if so,
then

select c.date,sum(total) from (
select a.date as date,a.total as total from table1 a
union all
select b.date as date,b.total as total from table2 b
) c group by date order by c.date;

if you need all date/total values:

select a.date,a.total from table1 a
union all
select b.date,b.total from table2 b;

try each to see if you like one or the other...

Sponsored Links