CODEWITHSUNDEEP
mysqlsqlsql-server
Chia Yong
Chia Yong

Reputation: 1

left join table with no duplicate

I have two tables -

t1:

col_1        col_2        col_3
10001        apple        3
10001        orange       2
10001        grapes       5

t2:

col_1        col_2        col_3        col_4
10001        apple        3            123
10001        orange       2            345

What SQL query would join these two tables; giving me a result like this:

col_1        col_2        col_3        col_4
10001        apple        3            123
10001        orange       2            345
10001        grapes       5

Upvotes: 0

Views: 363

Answers (4)

donL
donL

Reputation: 1300

SELECT t1.*, 
       col_4 
FROM   t1 
       LEFT JOIN t2 using(col_2);

If col_1 and col_2 identify a unique entry change the last line to:

LEFT JOIN t2 using(col_1,col_2);

If col_1, col_2, col_3 identify a unique entry change the last line to:

LEFT JOIN t2 using(col_1,col_2,col_3);

Upvotes: 0

idstam
idstam

Reputation: 2878

SELECT t1.col_1, t1.col_2, t1.col3, t2.col_4
FROM t1
LEFT OUTER JOIN t2 ON t1.col_1 = t2.col_1 AND t1.col_2 = t2.col_2 AND t1.col_3 = t2.col_3

Upvotes: 0

niktrs
niktrs

Reputation: 10066

Join tables, then use COALESCE to get the first non null data column

SELECT COALESCE(t1.col_1,t2.col_1) col_1,
   COALESCE(t1.col_2,t2.col_2) col_2,
   COALESCE(t1.col_3,t2.col_3) col_3,
   COALESCE(t2.col_4,'') col_4,
FROM t1
FULL JOIN t2 ON t1 ON t1.col_1 = t2.col1 AND t1.col_2 = t2.col_2 AND t1.col_3 = t2.col_3

Upvotes: 1

iHaveacomputer
iHaveacomputer

Reputation: 1437

(SELECT col_1, col_2, col_3 
FROM t1)

UNION DISTINCT

(SELECT col_1, col_2, col_3 
FROM t2)

Upvotes: 0

Related Questions