How to create rows combining data from two tables in a one-to-many relationship?

I have a table containing weather data by county:

CREATE TABLE weather (state_id TEXT, county_id TEXT, temperature REAL);

And a table containing one-to-many mappings between counties and zip codes (there are many zips per county). county_code in this table refers to the concatenation of state_id and county_id from the previous table.

CREATE TABLE zipcodes (county_code TEXT, zip TEXT);

How can I create a SELECT statement that maps zip codes to temperature? All zip codes in a county should get the same temperature. I am using BigQuery SQL. The tables are imported CSV, but I thought SQL-style CREATEs would be useful.

Not sure if this question belongs here or on DBA Stack Exchange, but here goes.

I dont have google big query, but based on my understanding of the question I think you just need a CONCAT in the join condition,let me know.

Fiddle in Mysql

SELECT
    z.county_code,
    z.zip,
    w.temperature
FROM
    zipcodes z
INNER JOIN
    weather w
ON
    z.county_code = CONCAT(w.state_id, w.county_id);