Table join or union?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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

Quoted text here. Click to load it

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,

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

if you need all date/total values:

select, from table1 a
union all
select, from table2 b;

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

Re: Table join or union?

Hi Callista


select date,sum(total1) as total1,sum(total2) as total2
from (
select date,total as total1,null as total2
from Table1
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.


Quoted text here. Click to load it

Site Timeline