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