Help Joining Two Tables in SQL?

  • Thread starter KataKoniK
  • Start date
  • Tags
    Sql
In summary, the conversation was about a user having trouble joining two tables and getting the desired result. They tried different joins and a union but were still unable to achieve the desired result. Another user provided a working query that involved joining the tables and including a null value for missing data. The user was able to successfully implement the query and thanked the other user for their help.
  • #1
KataKoniK
1,347
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.
 
Computer science news on Phys.org
  • #2
Try something like (tsql):
Code:
SELECT A, B, NULL AS 'C' FROM Table1
UNION ALL
SELECT A, B, C FROM Table2
 
  • #3
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
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
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
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
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
That worked. Thanks a lot for your help!
 
  • #9
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
 

FAQ: Help Joining Two Tables in SQL?

What is the purpose of joining two tables in SQL?

The purpose of joining two tables in SQL is to combine data from different tables that are related to each other. This allows for more complex and comprehensive queries to be made, as well as the ability to retrieve data from multiple tables at once.

What are the different types of joins in SQL?

The different types of joins in SQL are: inner join, left outer join, right outer join, and full outer join. These joins determine which records from the tables will be included in the result set based on the specified conditions.

How do you write a join query in SQL?

To write a join query in SQL, you need to use the SELECT statement followed by the columns you want to retrieve data from. Then, use the FROM clause to specify the two tables you want to join. Finally, use the JOIN keyword followed by the type of join and the condition to join the tables on.

What is the difference between inner join and outer join?

The main difference between inner join and outer join is that inner join only includes records that have matching values in both tables, while outer join includes all records from one table and only matching records from the other table.

How do you troubleshoot common errors when joining two tables in SQL?

Some common errors when joining two tables in SQL include specifying the wrong column or table name, using the wrong type of join, or not including the necessary conditions for the join. To troubleshoot these errors, double check your syntax and make sure you are using the correct column names and join type. You can also use the SQL error messages to identify any specific issues with your query.

Similar threads

Replies
31
Views
3K
Replies
51
Views
4K
Replies
1
Views
2K
Replies
67
Views
3K
Replies
8
Views
830
Replies
1
Views
1K
Replies
7
Views
412
Replies
7
Views
1K
Replies
1
Views
1K
Back
Top