CODEWITHSUNDEEP
mysql
Hugh_Kelley
Hugh_Kelley

Reputation: 1040

MYSQL, create row if value pair does not already exist

I have a table of yearly data for various countries.

Not all countries have the same number of years of data.

If there is no row for a country/year pair, the implication is that the value is 0.

I'm trying to build a query that will use a JOIN to add years and 0 for rows that don't yet exist.

current code:

CREATE TABLE years SELECT DISTINCT acq_year FROM final_data;

SELECT * FROM final_data CROSS JOIN years;

Upvotes: 0

Views: 37

Answers (1)

Booboo
Booboo

Reputation: 44148

I am assuming your table of yearly data is named final_data with columns country, acq_year and data:

    CREATE TEMPORARY TABLE years SELECT DISTINCT acq_year FROM final_data;

    CREATE TEMPORARY TABLE countries_years
        SELECT fd1.country, fd1.acq_year FROM
            (SELECT DISTINCT country, years.acq_year FROM final_data join years) fd1
            LEFT JOIN final_data fd2 ON fd1.country = fd2.country AND fd1.acq_year = fd2.acq_year
            WHERE fd2.acq_year IS null;

    INSERT INTO final_data(country, acq_year, data)
        SELECT country, acq_year, 0 FROM countries_years;

Upvotes: 1

Related Questions