Skip to content
Advertisement

Select that fill null fields using the id and the values of the same column

I have a table like this:

  PK |  IDs | name1 | name2
    -------------------
   1 |  1   |  a    | null  
   2 |  1   |  a    |  x
   3 |  2   |  b    | null  
   4 |  3   |  c    |  z
   5 |  2   | null  |  y
   6 |  1   | null  |  x
   7 |  2   |  b    | null
   8 |  2   | null  | null

And i want to execute a select in mySQL that give me an output like this:

  PK |  IDs | name1 | name2
    -------------------
   1 |  1   |  a    |  x
   2 |  1   |  a    |  x
   3 |  2   |  b    |  y
   4 |  3   |  c    |  z
   5 |  2   |  b    |  y
   6 |  1   |  a    |  x
   7 |  2   |  b    |  y
   8 |  2   |  b    |  y

So all the rows with the same id have the same name1 and name2 checking the one that its not null to fill it, if there is no one, it will continue as null.

Advertisement

Answer

If you only have one value of name1 or name2 for a given ID value, you can use an aggregation function like MAX (or MIN) which will give you that value from all the value for that IDs in the table. Using a derived table with those values, you can JOIN to the original table to get the name1 and name2 values for each PK, IDs combination:

SELECT d.PK, d.IDs, m.name1, m.name2
FROM data d
JOIN (SELECT IDs, MAX(name1) AS name1, MAX(name2) AS name2
      FROM data
      GROUP BY IDs) m ON m.IDs = d.IDs

Output:

PK  IDs     name1   name2
1   1       a       x
2   1       a       x
3   2       b       y
4   3       c       z
5   2       b       y
6   1       a       x
7   2       b       y
8   2       b       y

Demo on SQLFiddle

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement