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