Can I do this with SQL

[draissam]

Hi,

I have 3 tables with the following fields:

Table1: Account#; Project#; Business Unit#; Amount1
Table2: Account#; Project#; Business Unit#; Amount2
Table3: Account#; Project#; Business Unit#; Amount3

I need to join these 3 tables so that I have all the fields listed in one table (table4) as follow:

Account#; Project#; Business Unit#; Amount1;Amount2;Amount3

Also, I have some accounts that are repeated in table1, table2, and table3. How can i have such accounts come only once with their respective amounts listed under Amount1; Amount2; and Amount3.
Example: Account 600 is listed in table1 with Amount1 $100, and in table2 with Amount2 $200, and in table3 with Amount3 $300. In table4, I want one line for account 600 with Amount1 (100); Amount2 (200), Amount3 (300).

Thanks for your help.

 

[donutman]

Make this a view as follows:

Create View vwTables1n2n3
AS
Select Account#, Project#, [Business Unit#], Amount1, 0 Amount2, 0 Amount3
   from Table1
Union All
Select Account#, Project#, [Business Unit#], 0, Amount2, 0
   from Table2
Union All
Select Account#, Project#, [Business Unit#], 0, 0, Amount3
   from Table3

Now sum the amounts:

Select Account#, Project#, [Business Unit#], 
  Sum(Amount1) Amount1, Sum(Amount2) Amount2, Sum(Amount3) Amount3
From vwTable1n2n3 group by Account#, Project#, [Business Unit#]

-Karl

 

[Ginish]

Hi....

We can join all tables and then group by Account#, Project#, [Business Unit#]

Bye

Ginish

 

[swampBoogie]

select Account,project,unit,amount1,
(select amount 
   from table2 
  where Account = table1.account) as ammount2,
(select amount 
   from table3 
  where Account = table1.account) as ammount3
from table1

Change names as apropriate