Table join or union?

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?

Re: Table join or union?

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...

Re: Table join or union?

"callista" wrote in news:1142259355.391117.287540
@j52g2000cwj.googlegroups.com:

Hi Callista

Try

select date,sum(total1) as total1,sum(total2) as total2
from (
select date,total as total1,null as total2
from Table1
union
select date,null as total1,total as total2
from Table2) c
group by date

That should result in something like

date total1 total2
aaa bbb ccc
xxx yyy zzz

regardless of whether date is unique

You can use '0 as total1' and '0 as total2' if you prefer to return '0' for
dates without entries as opposed to 'null' - this may make it easier if you
want a fourth column such as 'sum(total1+total2) as total'

Hope that is what you need.

Jody

> 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?
>