Big Query - JOIN two tables

We have two tables like below:

Table A

 Name | Question  | Answer
 -----+-----------+-------
 Bob  | Interest  | art_and_theatre      
 Sue  | Interest  | finances_and_investments
 Sue  | Interest  | art_and_theatre
 Joe  | Interest  | cooking_and_nutrition
 Joe  | Interest  | nutrition_and_drinks 
 Bob  | Interest  | nutrition_and_drinks

Table B (Static)

           Interest                         |   Segment
--------------------------------------------+------------------
art_and_theatre                             |   S1
cooking_and_nutrition, nutrition_and_drinks |   S2 
finances_and_investments                    |   S3
finances_and_investments                    |   S4
technology                                  |   S5
telecommunications                          |   S6
art_and_theatre                             |   S7
art_and_theatre                             |   S8

Expected table

 Name | Question  | Answer
 -----+-----------+-------
 Bob  | Interest  | art_and_theatre      
 Sue  | Interest  | finances_and_investments
 Sue  | Interest  | art_and_theatre
 Joe  | Interest  | cooking_and_nutrition
 Joe  | Interest  | nutrition_and_drinks 
 Bob  | Interest  | nutrition_and_drinks
          (+)
 Bob  | Segment   | S1
 Bob  | Segment   | S7
 Bob  | Segment   | S8
 Sue  | Segment   | S3
 Sue  | Segment   | S4
 Sue  | Segment   | S1
 Sue  | Segment   | S7
 Sue  | Segment   | S8
 Joe  | Segment   | S2

As you can see, one user can have multiple interests and multiple interests can belong to one segment. Is this kind of JOIN is possible in Big Query?

from Recent Questions - Stack Overflow https://ift.tt/2HJZGFm
https://ift.tt/eA8V8J