One to many left join

ghz 8months ago ⋅ 50 views

Working in BigQuery I've got a table A with a column that has a item_id and a country_code and then another table B with item_id and country_region

A.country_code has values like 'US', 'UK', 'FR', 'DE', 'CA' etc. B.country_region has values like 'US', 'UK', 'FR', 'ANY', 'EU'etc.

I'm trying to left join b onto a but obviously struggling with how to handle the 'ANY' and 'EU' classes where they map to multiple rows.

Maybe I'm missing something obvious here? I'd like to just have some conditional logic that handles how I'm joining, but I don't know if that's possible?

Answers

You can handle the 'ANY' and 'EU' values in table B by using conditional logic in your join condition. Here's how you can achieve this in BigQuery:

SELECT A.*, B.*
FROM table_A A
LEFT JOIN table_B B 
ON A.item_id = B.item_id 
AND (B.country_region = A.country_code OR B.country_region IN ('ANY', 'EU'));

In this query:

  • We perform a left join between table A and table B.

  • We use two conditions in the join:

    1. A.item_id = B.item_id: This ensures that we join rows from table B where the item_id matches between the two tables.

    2. (B.country_region = A.country_code OR B.country_region IN ('ANY', 'EU')): This handles the special cases where 'ANY' or 'EU' appear in table B. If the country_region in table B matches the country_code in table A, or if the country_region is 'ANY' or 'EU', the rows will be joined.

This way, you'll be able to handle the join between the tables, including the special cases for 'ANY' and 'EU' in table B.

#sql