CODEWITHSUNDEEP
sqlpostgresqlleft-join
Rovex X
Rovex X

Reputation: 13

Postgresql SELECT LEFT JOIN with columns on case

SELECT a1,a2,a3,a4,count(a5),b1,b2,b3
FROM table1
LEFT JOIN table2 ON a1=b1 AND a2=b2 (*here i need to join
              next columns a3=b3 only if from table2 will be returned more than 1 records 
    other wise first 2 columns will be enough*)
group by a1,a2,a3,a4,a5,b1,b2,b3

Anybody knows how to perform this trick ?

Upvotes: 0

Views: 369

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Well, if I understand correctly:

FROM table1 t1 LEFT JOIN
     (SELECT t2.*, COUNT(*) OVER (PARTITION BY b1, b2) as cnt
      FROM table2 t2
     )
     ON t1.a1 = t2.b1 AND t1.a2 = t2.b2 AND
        (cnt = 1 OR t1.a3 = t2.a3)

Upvotes: 1

Related Questions