MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.36


CREATE TABLE weather (
    state_id VARCHAR(2),
    county_id VARCHAR(3),
    temperature REAL
);

-- Create the zipcodes table
CREATE TABLE zipcodes (
    county_code VARCHAR(5),
    zip VARCHAR(5)
);
INSERT INTO weather (state_id, county_id, temperature) VALUES
('CA', '001', 75.5),
('CA', '002', 80.1),
('TX', '001', 92.3);

INSERT INTO zipcodes (county_code, zip) VALUES
('CA001', '90001'),
('CA001', '90002'),
('CA002', '90210'),
('TX001', '75001'),
('TX001', '75002');

Records: 3  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0


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);


county_code
zip
temperature


CA001
90001
75.5

CA001
90002
75.5

CA002
90210
80.1

TX001
75001
92.3

TX001
75002
92.3

county_code	zip	temperature
CA001	90001	75.5
CA001	90002	75.5
CA002	90210	80.1
TX001	75001	92.3
TX001	75002	92.3