Join postgres table on two columns?

postgresql

I can't find a straightforward answer. My query is spitting out the wrong result, and I think it's because it's not seeing the "AND" as an actual join.

Can you do something like this and if not, what is the correct approach:

SELECT * from X
LEFT JOIN Y
ON
  y.date = x.date AND y.code = x.code

This is possible:

The ON clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a WHERE clause. A pair of rows from T1 and T2 match if the ON expression evaluates to true for them.

http://www.postgresql.org/docs/9.1/static/queries-table-expressions.html#QUERIES-FROM

Your SQL looks OK.

It's fine. In fact, you can put any condition in the ON clause, even one not related to the key columns or even the the tables at all, eg:

SELECT * from X
LEFT JOIN Y
    ON y.date = x.date
    AND y.code = x.code
    AND EXTRACT (dow from current_date) = 1

Join postgres table on two columns?

Related

Recent Posts