CODEWITHSUNDEEP
sql-serverjoin
Fearghal
Fearghal

Reputation: 11407

How do i join all data in 1 col of a table2 to table1 whilst keeping non matching data in table1

I am trying to join information to my table 1 from table 2 without losing any data in table1. I have tried inner, full outer, left and right joins but all bring same result - removing some table1 data rows.

table 1

Col1, Col2, col3
1, mary, null
2, john, null
3, sally, 25
4, barry, 15

table 2

Col1, Col2
2, fireman, 1
3, office, 2
4, teacher, 3

desired result

col1, col2, col3, col4
1, mary, null, null
2, john, null, fireman
3, sally, 25, office
4, barry, 15, teacher

what i get;

2, john, null, fireman
3, sally, 25, office
4, barry, 15, teacher

With my query

Select col1, col2, col3 from table1 left join col2 on table1.col1=table2.col1 order by table2.col3 asc

Upvotes: 0

Views: 48

Answers (2)

Linial
Linial

Reputation: 1154

Okay I've built it according to your desired solution:

SELECT t1.col1, t1.col2, t1.col3, t2.col2 AS col4 
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
ORDER BY t2.col2 ASC

SQLFiddle Demo

You could review the fiddle above.

good luck!

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT   T1.col1, 
         T1.col2, 
         T1.col3, 
         T2.col2 AS col4
FROM     Table1 T1
         LEFT JOIN Table2 T2
             ON T1.col1 = T2.col1 
ORDER BY T2.col2 ASC

Fiddle Demo

Upvotes: 1

Related Questions