join multiple tables

[xdia]

Hello I need ways to accomplish the following

tbl1
name sales
a 10
b 20
c 30

tbl2
name sales
b 50
c 60
d 70


tbl3
name sales

a 30
c 40
x 40

I want it to be like below:

tbl1 tbl2 tbl3
a 10 30
b 20 50
c 30 60 40
d 70
x 40



Thanks in advance~~

 

[xdia]

One more thing all data comes from one table and tble a,b,c I have created from different period such as this year, last year, this month. What would be the best way to do this???

 

[r937]

use crosstab on this query --

select name, sales as tbl1
  from tbl1
union all
select name, sales as tbl2
  from tbl2
union all
select name, sales as tbl3
  from tbl3

in fact, you could probably adapt this so that you wouldn't need to split the data out of the original table into three tables, you could just do it in three WHERE clauses above...

rudy

http://rudy.ca/

 

[r937]

whoops, that was sloppy

try this instead --

select name, sales, 'tbl1' as tablename
  from tbl1
union all
select name, sales, 'tbl2' as tablename
  from tbl2
union all
select name, sales, 'tbl3' as tablename
  from tbl3

 

[xdia]

Hey thanks for the reply and try the second method but it gives me the sum of the sales amout instead of the result I want. Am I doing something wrong?