Home How can I normalize a MySQL table that contains NULLs in some fields for some records but not all?

Questions

How can I normalize a MySQL table that contains NULLs in some fields for some records but not all?

March 14, 2023

I have a non-normalized table that’s arisen from joining three different tables together that are joining against the PrimaryId field. Most fields have valid values for all fields, but some are missing values in identifiers

PrimaryId, Name, Internal_System_One_Identifier, Internal_System_Two_Identifier, 
abc123, 'Joes Supermarket', '22e0b9f6', NULL
abc123, 'Joes Supermarket', NULL, '22e0b9f8'
abc124, 'Suzannas Coffee', '22e0ba6c', NULL
abc124, 'Suzannas Coffee', '22e0ba6c', '22e0ba6e'
abc125, 'Rogers Aquatics', NULL, '22e0ba6c'
abc125, 'Rogers Aquatics', '22e0ba6e', '22e0ba6c'
abc126, 'Frankies Records', NULL, NULL
abc127, 'Henrys Hammers', '22e0bb7d','22e0bb7e'

And I want to normalize it to

abc123, Joe's Supermarket, ac341d-4a4fe9, 9db37d-8e5g73
abc124, 'Suzannas Coffee', '22e0ba6c', '22e0ba6c'
abc125, 'Rogers Aquatics', '22e0ba6e', '22e0ba6c'
abc126, 'Frankies Records', NULL, NULL
abc127, 'Henrys Hammers', '22e0bb7d','22e0bb7e'

But I can’t figure out how to do this at all. I feel like I should use something like a coalesce function or window function, but I can’t understand how I would do that.

>Solution :

You could group by the primary ID and the name, and use max on the other two identifiers in order to eliminate nulls:

SELECT    PrimaryId,
          Name,
          MAX(Internal_System_One_Identifier),
          MAX(Internal_System_Two_Identifier)
FROM      mytable
GROUP BY PrimaryId, Name