New table from Multiple Tables

[Ebes1099]

I am trying to combine data from multiple tables into one master table.

Each table has a Field called [MbrID], [Amt_Cost_Tbl1], [Amt_Paid_Tbl1], [Amt_Cost_Tbl2], [Amt_Paid_Tbl2], etc.. The Tbl1, Tbl2, etc are specific to the table names and I would like to keep them that way. I have 4 tables, so I should end with 9 columns. One MbrID column and [Amt_Cost_Tbl1], [Amt_Paid_Tbl1], [Amt_Cost_Tbl2], [Amt_Paid_X2]...

I can't do a Union Query because they don't have the same columns and I want to keep the values separate. If a MbrID is in one table but not the others, they should get a 0 for the Amount fields in the table they are not in (or a Null is fine).

I was trying to do this with multiple joins and left joins but that wasn't getting the outputs I was expecting. Join had too few since not every member was in every table and the left join had way too many.

 

[gmmastros]

I can't do a Union Query because they don't have the same columns and I want to keep the values separate.

Even if they don't have the same columns, you can "force" it to work. Ex:

Select MbrId,
Amt_Cost As Amt_Cost_Tbl1,
Amt_Paid As Amt_Paid_Tbl1,
NULL As Amt_Paid_Tbl2,
NULL As Amt_Paid_Tbl2
From Tbl1

Union All

Select MbrId,
NULL,
NULL,
Amt_Cost,
Amt_Paid
From Tbl2
[/code]

Notice that I use NULL in the first query for table 2 columns and I use NULL in the second query for table 1 columns. This union query works because each query has the same number of columns.

Now... based on your description, I'm not 100% sure this is what you ultimately want because you will get multiple rows for each MbrID. If this is not what you want, let me know and I will take another look at this.




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I messed up the code blocks on my previous post. Sorry.

Select MbrId, 
       Amt_Cost As Amt_Cost_Tbl1, 
       Amt_Paid As Amt_Paid_Tbl1, 
       NULL As Amt_Paid_Tbl2, 
       NULL As Amt_Paid_Tbl2
From   Tbl1

Union All

Select MbrId, 
       NULL, 
       NULL, 
       Amt_Cost, 
       Amt_Paid 
From   Tbl2

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom