By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Joing tables using more than one field

P: n/a
I have two tables I need to join but there are 2 fields which they
could be joined on.

Using the example Tablles, TableA and TableB below;

TableA
ID1 ID2 Qty
1 Null 4
2 A 5
Null B 6

TableB
ID1 ID2 Qty
Null A 6
3 B 6
4 Null 7
Null C 8

I want to create TableC which will look like this;
ID1 ID2 TableA.Qty Tableb>Qty
1 Null 4 Null
2 A 5 6
3 B 6 6
4 Null Null 7
Null C Null 8
Any ideas?

Regards,
Ciarán

Mar 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try:

select
*
from
TableA a
join
TableB b on b.ID1 = a.ID1 and b.ID2 = a.ID2

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<ch********@hotmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
I have two tables I need to join but there are 2 fields which they
could be joined on.

Using the example Tablles, TableA and TableB below;

TableA
ID1 ID2 Qty
1 Null 4
2 A 5
Null B 6

TableB
ID1 ID2 Qty
Null A 6
3 B 6
4 Null 7
Null C 8

I want to create TableC which will look like this;
ID1 ID2 TableA.Qty Tableb>Qty
1 Null 4 Null
2 A 5 6
3 B 6 6
4 Null Null 7
Null C Null 8
Any ideas?

Regards,
Ciarán

Mar 21 '06 #2

P: n/a
select coalesce(a.ID1,b.ID1),
coalesce(a.ID2,b.ID2),
a.Qty,
b.Qty
from TableA a
full outer join TableB b on a.ID1=b.ID1 or a.ID2=b.ID2

Mar 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.