0

How can I best do a waterfall join in SQL

SELECT * FROM A
LEFT JOIN B
ON A.col1=B.col1 AND
   A.col2=B.col2 

but for the ones where there's no match only join on col1 and ignore col2:

LEFT JOIN B
   ON A.col1=B.col1 AND
1
1

Never heard of a waterfall join, but based on your description:

SELECT A.*, COALESCE(B.col3, C.col3) AS col3 ...
  FROM A
  LEFT JOIN B
    ON A. col1 = B.col1
    AND A.col2 = B.col2
  LEFT JOIN B AS C
    ON A.col1 = C.col1
    AND B.col1 IS NULL;

Not the answer you're looking for? Browse other questions tagged or ask your own question.