Question on Joining tables - SQL Server Forums

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

Question on Joining tables

Author

Topic

marmias
Starting Member

2 Posts

Posted - 2009-07-12 : 00:52:07

Hello,

I have three tables: Contracts1, Contracts2 and GoodCustomers.

These tables have the following fields:

Contracts1.ContractNo, Contracts1.CustomerNo

Contracts2.ContractNo, Contracts2.CustomerNo

GoodCustomers.CustomerNo, GoodCustomers.CustomerName

Contracts1 has about 200000 rows, whereas Contracts2 has about 300000 rows. GoodCustomers has 1000 rows.

I need to return the customers in GoodCustomers (i.e. all the fields from GoodCustomers) along with their contract number(s) in the field ContractNo (a customer may have one or more contracts in Contracts1 and/or one or more contracts in Contracts2).

I think an easy way to do this is the following:


select a.*, b.ContractNo
from GoodCustomers a
inner join 
(
select *
from Contracts1
union
select *
from Contracts2
) b
on a.CustomerNo=b.CustomerNo

But is this the best/most efficient way to do this?
Is there a better way to get the same results?

Many thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 03:23:31

you want to return the entire matching records from contracts1/contracts2 or just one per CustomerNo?

marmias
Starting Member

2 Posts

Posted - 2009-07-12 : 13:01:22

I want to return all the matching records from contracts1 and contracts2.

That is, there may be multiple rows with the same customerNo, but with different contractNo in each row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 13:14:16

then its just a matter of joining tables on customer no field as you did

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources