Combine data from 3 different tables

[udigold1]

Hi, I have 3 tables (Table1 to Table3 in the following example), and I want to combine that with a sql statement so it would give a result shown in "output" (to be shown on datagrid, for example)

Code:

Table1_Customer 
C_ID C_Name 
1    James 
2    Mike 
3    John  

Table2_DataType 
D_ID D_Name 
1     Deposit 
2     Withdraw 
3     Yield  

Table3_Numbers 
O_ID C_ID N_value 
1      1     10000 
1      1     1500  
2      1     450 
3      1     1.5 
3      2     2.2 
3      3     0.4 
1      2     10000 
1      2     2500  
2      2     450 1      
3     11000  

Output: 
C_Name Deposit Withdraw Yield 
James   11500   450     1.5 
Mike    12500   450     2.2 
John    11000   0       0.4

I need the final output to show just one unique line for each name, and not repeating lines, and also to sum data.

Thanks,

Udi

[TheShabz]

TRANSFORM Sum(tblNumbers.nValue) AS SumOfnValue
SELECT Table1_Customer.C_Name
FROM Table2_DataType INNER JOIN (Table1_Customer INNER JOIN Table3_Numbers ON Table1_Customer.C_ID = Table3_Numbers.C_ID) ON Table2_DataType.D_ID = Table3_Numbers.D_ID
GROUP BY Table1_Customer.C_Name
PIVOT Table2_DataType.D_Name;

Note: I changed your Table3_Numbers.O_ID to Table3_Numbers.D_ID as I think it was a typo. If not, go ahead and change it back.

[udigold1]

Hi,

Thanks for the answer!
If I may ask another two related questions:
1. What happens if there's a fourth table, like in the following example?
2. Is there a way to add another column to the output that show Net deposits (deposits - withdraw)?


Thanks again!

Table1_Customer
C_ID C_Name
1    James
2    Mike
3    John

Table2_DataType
D_ID D_Name
1     Deposit
2     Withdraw
3     Yield


Table21_DataBankType
DB_ID DB_Name               D_ID 
41    Deposit from Bank A   1 
42    Deposit from Bank B   1
43    Withdraw from Bank A  2
44    Withdraw from Bank B  2
45    Yield                 3


Table3_Numbers
DB_ID C_ID N_value
41      1     10000
42      1     1500 
44      1     450
45      1     1.5
45      2     2.2
45      3     0.4
42      2     10000
41      2     2500 
43      2     450
42      3     11000

Output:
C_Name Deposit Withdraw Yield
James   11500   450     1.5
Mike    12500   450     2.2
John    11000   0       0.4

Thanks

[TheShabz]

Do a google search on cross-tab queries. You'll find all you need to know.