How to add/fill in the missing sequence/data to the records using MySQL-CodePudding

I have the dataset like:

material  location  value
123        p1         5
123        p1         3
123        p2         1
456        p1         2
456        p3         2
456        p3         2

total distinct values of location possible is p1,p2,p3. It we notice, material 123 is missing p3 info hence one record will inserted like 123 p3 0. similarly for 456, its 456 p2 0.

output hence is ,

123        p1         5
123        p1         3
123        p2         1
123        p3         0
456        p1         2
456        p3         2
456        p3         2
456        p2         0

cross join is not performance effective, Hence please let me know if any other method.

CodePudding user response：

SELECT material, location, COALESCE(test.value, 0) value
-- materials list not present - gathering from the data
FROM ( SELECT DISTINCT material
       FROM test ) materials
-- total distinct values of location possible is p1,p2,p3 - hardcoding
CROSS JOIN ( SELECT 'p1' location 
             UNION ALL
             SELECT 'p2'
             UNION ALL
             SELECT 'p3' ) locations
NATURAL LEFT JOIN test
ORDER BY 1, 2;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fcc1785255d365df753c3b68484906a2

If the tables with materials list and locations list exists then use them instead of subqueries.

CodePudding user response：

In general:

SELECT ...,
       COALESCE(y.value, 0),
       ...
    FROM table_of_all_possible_values AS a
    LEFT JOIN your_table AS y  ON (y.col = a.col)

LEFT JOIN y says that if the row is missing from y, it will still match, but all columns will be NULL.

Note that COALESCE(NULL, 0) is "0". "0" is typically what is wanted in this situation.

If you are using MariaDB, see the pseudo table seq_1_to_3, which would magically build a table with values "1,2,3". For MySQL, see Akina's Answer for a way to use UNION.