How to join tables with different columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to join tables with different columns

Author

Topic

mmclaughlin
Starting Member

6 Posts

Posted - 2009-04-29 : 11:00:07

I'm very new at SQL and I'm trying to get results from multiple tables into a new table with the common factor coming from a column in one of the tables.

Let me break it down....

I have 12 tables, each have different column names BUT has one in common called "cubs_id"

I want to select from all 12 tables ONLY if the "group_name" column in one of the tables is equal to "Closed Cases" and then I want to grab all those entries and grab the same entries in the other tables by using the common column "cubs_id".

Please help me out if you can....

Thanks

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-29 : 11:06:06

SELECT * FROM [table1] a INNER JOIN [table2] b ON a.cubs_id = b.cubs_id AND a.group_name = b.group_name INNER JOIN [table3] c ON a.cubs_id = c.cubs_id AND a.group_name = c.group_name......etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 11:06:38

[code]
select required columns here....
from table1 t1
join table2 t2 on t2.cubs_id=t1.cubs_id
join table3 t3 on t3.cubs_id=t1.cubs_id
join table4 t4 on t4.cubs_id=t1.cubs_id
....
join table12 t12 on t12.cubs_id=t1.cubs_id
where group_name='Closed Cases'
[/code]

mmclaughlin
Starting Member

6 Posts

Posted - 2009-04-29 : 11:37:03

I get an error when I try this test query...

select *
from [dbo].[Case_Basic] t1
join [dbo].[Case_Financial] t2 on t2.cubs_case_id=t1.cubs_case_id
join [dbo].[Children_Basic] t3 on t3.cubs_case_id=t1.cubs_case_id
where group_name='Closed Cases'

Error:

- Setting Source Connection (Error)
Messages
Error 0xc0207015: Source - Query [1]: There is more than one data source column with the name "cubs_case_id". The data source column names must be unique.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0207015 (Microsoft.SqlServer.DTSPipelineWrap)

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-29 : 11:40:17

You cannot do * on multiple tables which has the same column names.

So you'd need to manually define them ex:
SELECT t1.cubs_id, t1_group_name... etc (only define the ones you need).

mmclaughlin
Starting Member

6 Posts

Posted - 2009-04-29 : 11:58:30

I understand what you said and thank you for helping. Let me start over here so you know exactly what I would like to happen....

I have an SQL database with 12 tables. All tables are linked to one commmon column "cubs_case_id". What I am trying to do is to export the data in groups. For example, we have 60,000 records and in group 1 we want to export only 10,000 using a column "group_name" that exists in 1 of the tables "Case_Basic". What I want as a result is to have 12 new tables with just those 10,000 records and then go forard with other groups as I need them. Does this make sense to you?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 13:26:02

my earlier suggestion should work fine for you


select required columns here.... INTO YourDestinationTable
from table1 t1
join table2 t2 on t2.cubs_id=t1.cubs_id
join table3 t3 on t3.cubs_id=t1.cubs_id
join table4 t4 on t4.cubs_id=t1.cubs_id
....
join table12 t12 on t12.cubs_id=t1.cubs_id
where group_name='Closed Cases'

make sure you replace table1,table2,... with actual names

mmclaughlin
Starting Member

6 Posts

Posted - 2009-04-29 : 14:45:19

Ok we are getting closer on this! This is the new error that came about and I can't see where it is...

It says "Invalid Column Name 'cubs_case_id' "

Here is the query:

SELECT [cubs_case_financial_guid]
,[cubs_case_id] = t1.cubs_case_id
,[k_claim_amt]
,[orig_claim_amt]
,[arrears_calc_from_dt]
,[arrears_calc_to_dt]
,[arrears_contract_balance]
,[arrears_dt_payout_projected]
,[initial_balance_amt]
,[initial_interest_amt]
,[initial_principle_amt]
,[cse_balance_amt]
,[cse_interest_amt]
,[cse_principle_amt]
,[monthly_interest_amt]
,[ncp_balance_amt]
,[ncp_interest_amt]
,[ncp_principle_amt]
,[total_obligation_amt]
,[current_monthly_sppt_dt]
,[total_monthly_wagewithholding_amt]
,[current_monthly_sppt_due_1]
,[current_monthly_sppt_due_2]
,[current_monthly_sppt_due_3]
,[current_monthly_sppt_due_4]
,[current_monthly_sppt_due_5]
,[arrears_mnthly_amt_child_1]
,[arrears_mnthly_amt_child_2]
,[arrears_mnthly_amt_child_3]
,[arrears_mnthly_amt_child_4]
,[arrears_mnthly_amt_child_5]
,[ww_final_stop_dt]
,[ww_stop_dt_period_1]
,[ww_stop_dt_period_2]
,[ww_stop_dt_period_3]
,[ww_stop_dt_period_4]
,[ww_stop_dt_period_5]
,[current_interest_rate]
,[interest_state_id]
,[interest_sum_amt]
,[judgement_interest_rate_simple_yn]
,[first_payment_amt]
,[first_payment_dt]
,[admin_fee_amt]
,[admin_fee_paid_amt]
,[admin_fee_balance_amt]
,[pass_thru_amt]
,[pass_thru_dt]
,[ar_verified_amt]
,[ar_verified_dt]
,[contract_template_id]
,[cp_fee]
,[contract_description]
,[cp_fee_comm_rate]
,[principal_comm_rate]
,[cp_fee_collected]
,[nsf_fee]
,[last_payment_dt]
,[last_payment_amt]
,[assigned_amount]
,[assigned_amount_collected]
,[interest_owing]
,[interest_collected]
,[cancelled_assigned_amt]
,[cancelled_other_amt]
,[court_owing]
,[court_collected]
,[attorney_collected]
,[attorney_owing]
,[misc_owing]
,[misc_collected]
,[cubs_contract_balance]
from [dbo].[Case_Basic] t1
join [dbo].[Case_Financial] t2 on t2.cubs_case_id=t1.cubs_case_id
join [dbo].[Children_Basic] t3 on t3.cubs_case_id=t1.cubs_case_id
join [dbo].[Custodial_Parent] t4 on t4.cubs_case_id=t1.cubs_case_id
join [dbo].[Employers] t5 on t5.cubs_case_id=t1.cubs_case_id
join [dbo].[NonCustodial_Parent] t6 on t6.cubs_case_id=t1.cubs_case_id
join [dbo].[Court_Order_Basic] t7 on t7.cubs_case_id=t1.cubs_case_id
join [dbo].[Gov_Entity_Library] t8 on t8.cubs_case_id=t1.cubs_case_id
join [dbo].[Memo] t9 on t9.cubs_case_id=t1.cubs_case_id
join [dbo].[Lien_Activity] t10 on t10.cubs_case_id=t1.cubs_case_id
join [dbo].[Voluntary_Pay_Plan] t11 on t11.cubs_case_id=t1.cubs_case_id
join [dbo].[Wage_Withholding_Work_Orders] t12 on t12.cubs_case_id=t1.cubs_case_id
where group_name='Closed Cases'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-29 : 15:23:12

You could try the ANSI standard way of aliasing..

quote:
Originally posted by mmclaughlin

Ok we are getting closer on this! This is the new error that came about and I can't see where it is...

It says "Invalid Column Name 'cubs_case_id' "

Here is the query:

SELECT [cubs_case_financial_guid]
,t1.cubs_case_id AS cubs_case_id
,[k_claim_amt]
,[orig_claim_amt]
<snip>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-07 : 12:51:53

check if all tables have the reffered field?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources