google bigquery - Join two arrays on same id

Question

I have two tables:

• table one with visit_id, revenue
• table two with visit_id, array_products_visited with an array of products visited on my website
• table three with visit_id, array_bought with an array of products bought on my website

When I join the everything, the products bought (table three) appear repeated by each and every products visited. How can I avoid this?

Thanks.

Edit: to help you understand what I mean

table one:

visit_id
123456
123457

question from:https://stackoverflow.com/questions/65889207/join-two-arrays-on-same-id

Answer 1

You need to add a WHERE clause that limits the rows, so you only see the ones where two.user_id=three.userid.