Question

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

  • 8 October 2020
  • 0 replies
  • 853 views

Userlevel 6
Badge +1

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 replies

Be the first to reply!

Reply