?
Solved

SQL Join

Posted on 2009-04-17
13
Medium Priority
?
247 Views
Last Modified: 2012-05-06
What is the syntax to join TABLE1 and TABLE2 to get TABLE3?

TABLE1

CommonID Field1
A 1
A 1
B 2
B 2

TABLE2

CommonID Field2
A 100
A 100
C 300
C 300

TABLE3

CommonID Field1 Field2
A 1 100
B 2 NULL
C NULL 300
0
Comment
Question by:risk_prod
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 13

Expert Comment

by:sm394
ID: 24172332
select distinct *t1.commonid,t1.field1, t2.field2
from table1 t1
left join table2 t2
on t1.commonID=t2.commonid
0
 
LVL 13

Expert Comment

by:sm394
ID: 24172335
sorry ignore my last post

select distinct t1.commonid,t1.field1, t2.field2
from table1 t1
left join table2 t2
on t1.commonID=t2.commonid
0
 

Author Comment

by:risk_prod
ID: 24172350
sm394 - this will get the distinct values of commonid in t2 not in t1 as well?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:8080_Diver
ID: 24172358
Slight change in the previously recommended SQL: ;-)
select distinct t1.commonid,t1.field1, t2.field2
from table1 t1
FULL OUTER join table2 t2
on t1.commonID=t2.commonid

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24172363
Come to think of it, you don't really need the DISTINCT:
select t1.commonid,t1.field1, t2.field2
from table1 t1
FULL OUTER join table2 t2
on t1.commonID=t2.commonid

Open in new window

0
 

Author Comment

by:risk_prod
ID: 24172387
8080 - same question to you. Will your code pick up distinct values in t2 NOT IN t1? Because I don't think sm's code did not the trick. The join did not pick up the C ID.
0
 
LVL 13

Accepted Solution

by:
sm394 earned 500 total points
ID: 24172401
that will do

select  t1.commonid,t1.Field1, t2.field2
from tablea t1
left outer join tableb t2 on t1.commonid=t2.commonid
union
select  t2.commonid,t1.Field1, t2.field2
from tablea t1
right outer join tableb t2 on t1.commonid=t2.commonid
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24172415
This will address your problem
select	coalesce(t1.commonid, t2.commonid) as commonid,
		t1.field1, 
		t2.field2
from table1 t1
FULL OUTER join table2 t2
on t1.commonID=t2.commonid

Open in new window

0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 24172432
oops, you really need the DISTINCT clause
select	distinct coalesce(t1.commonid, t2.commonid) as commonid,
		t1.field1, 
		t2.field2
from table1 t1
FULL OUTER join table2 t2
on t1.commonID=t2.commonid

Open in new window

0
 
LVL 13

Expert Comment

by:sm394
ID: 24172453
in ralmada query we need to add distinct

select  distinct coalesce(t1.commonid, t2.commonid) as commonid,
                t1.field1,
                t2.field2
from table1 t1
FULL OUTER join table2 t2
on t1.commonID=t2.commonid
0
 
LVL 13

Expert Comment

by:sm394
ID: 24172458
@ralmada    sorry for duplicate :)
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24172460
no worries :)
0
 

Author Comment

by:risk_prod
ID: 24172522
sm394, ralmada - both of your codes worked, thanks a lot.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question