Help Joining Two Tables in SQL?

  • Thread starter KataKoniK
  • Start date
  • #1
168
0
I'm having trouble getting the following work. Can anyone help me?

I have two tables that look like the following:

Code:
Table 1                 Table 2

A B                     A B C
1 4                     1 4 6
2 5                           
3 6

How do I join both of them such that the end result would be this?

Code:
Result

A B C
1 4 6
2 5 null
3 6 null

I tried to do the right, left, normal joins, and union, but I still cannot get it to show like the resulting table. Any help would be great, thanks.
 

Answers and Replies

  • #2
-Job-
Science Advisor
1,151
2
Try something like (tsql):
Code:
SELECT A, B, NULL AS 'C' FROM Table1
UNION ALL
SELECT A, B, C FROM Table2
 
  • #3
168
0
Thanks for the reply. I just gave that a try and it did the following:

Code:
Result

A B C
1 4 
2 5 
3 6 
1 4 6

I'm using PSQL PostgresSQL to do this stuff. Should it be the same with your tsql code? Because I get the table above when doing your example.
 
  • #4
-Job-
Science Advisor
1,151
2
Sorry, i misread what you wanted to do. How about the following:
Code:
SELECT t1.A, t1.B, t2.C
	FROM Table1 AS t1
	JOIN Table2 AS t2 ON (t1.A = t2.A AND t1.B = t2.B)
 
  • #5
-Job-
Science Advisor
1,151
2
Ok, the following worked for me:
Code:
SELECT t1.A, t1.B, t2.C
	FROM Table1 AS t1
	JOIN Table2 AS t2 ON (t1.A = t2.A AND t1.B = t2.B)
UNION ALL
SELECT t1.A, t1.B, NULL AS 'C'
	FROM Table1 AS t1
	JOIN Table2 AS t2 ON (NOT t1.A = t2.A)
 
  • #6
168
0
Thanks, that worked, but what do you need to add if there are duplicate entires in Table 1?

ie.

Code:
Table 1

A B 
1 4 
1 4
2 5 
3 6

Using the latest query you wrote, I get the resulting table:

Code:
Table 1

A B C
1 4 6
2 5 
3 6

One of the same 1 4 entry is removed. I tried taking out the NOT, but still the same result.
 
  • #7
-Job-
Science Advisor
1,151
2
That doesn't happen to me, it must be a PSQL implementation.
Anyway, the following should work for you, even if it's not optimal:
Code:
SELECT 
t1.A, 
t1.B, 
(SELECT t2.C FROM Table2 AS t2 WHERE t2.A = t1.A AND t2.B = t1.B) AS 'C' 
FROM Table1 AS t1
 
  • #8
168
0
That worked. Thanks a lot for your help!
 
  • #9
verty
Homework Helper
2,182
198
My first try would be:
select t1.A, t1.B, t2.C
from Table1 t1
left outer join Table2 t2 on t1.A = t2.A and t1.B = t2.B
 

Related Threads on Help Joining Two Tables in SQL?

Replies
31
Views
2K
  • Last Post
Replies
5
Views
2K
Replies
5
Views
2K
  • Last Post
Replies
12
Views
1K
  • Last Post
Replies
5
Views
2K
Replies
1
Views
790
Replies
8
Views
2K
Replies
5
Views
4K
Replies
8
Views
1K
Replies
1
Views
1K
Top