Conditional JOIN BigQuery - equality of fields from both sides of the join error

Dawid
Participant V

I hate this error:

Query execution failed: - LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

But I’m hoping I just need to adjust my logic. My mind, however, is blank…

Let’s say I have table users with user_id and type (seller, buyer).

Then I have orders that contains both seller_id and buyer_id.

I wanted to simply join it like this:

SELECT
  *
FROM users
  LEFT JOIN orders ON users.user_id = orders.seller_id OR users.user_id = orders.buyer_id

I want to do this because users can be sometimes sellers sometimes buyers but I think I’m missing something here.

I also tried this

LEFT JOIN orders on users.user_id = IF(users.type = "seller", orders.seller_id, orders.buyer_id)

but got the same error.

Essentially it becomes many_to_many relationship because one order can belong to two people, just not sure if this is the best way to go about it.

Normally I would create two joins, not sure why I got so adamant about doing it this way

0 0 3,692
0 REPLIES 0
Top Labels in this Space
Top Solution Authors