CODEWITHSUNDEEP
sqloracle-databaseleft-join
v.montag
v.montag

Reputation: 91

Join tables and choose one value of one to many

I need a join with two tables, but its possible that there is more than one match

t1              t2
A   B           A   C
1   x           1   0
2   x           1   1
3   x           2   0
4   x           3   1
5   x           4   1
6   x           4   0
                5   0
                6   1

Now I need a left join from t1 and t2 with the option, if there is C 0 and 1 with on a selected A, so C must be 0 (for A=1 and A=4) .

So the result should be :

t3     
A   B   C  
1   x   0  
2   x   0  
3   x   1  
4   x   0  
5   x   0  
6   x   1

Is this possible ? Thanks in advance.

Upvotes: 1

Views: 61

Answers (2)

sagi
sagi

Reputation: 40481

You can do it with a join and group by :

SELECT t1.a,t1.b,MIN(t2.c)
FROM t1
LEFT JOIN t2
 ON(t1.a = t2.a)
GROUP BY t1.a,t1.b

MIN() pick the smallest out of all the result set for each group, so in case both 1 and 0 will be returned, MIN() will display 0 because its smaller.

Upvotes: 3

MT0
MT0

Reputation: 167972

SELECT t1.A, B, C
FROM   t1
       LEFT OUTER JOIN
       ( SELECT A, C
         FROM   (
           SELECT A, C,
                  ROW_NUMBER() OVER ( PARTITION BY A ORDER BY C ) AS rn 
           FROM   t2
         )
         WHERE rn = 1
       ) t2
       ON ( t1.A = t2.A )

or 

SELECT t1.A, B, C
FROM   t1
       LEFT OUTER JOIN
       ( SELECT A, MIN(C) AS C
         FROM   t2
         GROUP BY A
       ) t2
       ON ( t1.A = t2.A )

Upvotes: 1

Related Questions