469,076 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

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?

Mar 13 '06 #1
2 1262
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...

Mar 13 '06 #2
"callista" <ca******@gmail.comwrote 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?
Oct 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Stephen Patten | last post: by
4 posts views Thread by SQLJunkie | last post: by
5 posts views Thread by Arvin Portlock | last post: by
1 post views Thread by cashotte | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.