MySQL: Merge rows and returning default values

Question

Imagine the following table:

id | variant | name
-----------------------
1  | 1       | a
1  | 2       | b
1  | 3       | c
2  | 1       | d
2  | 2       | e
2  | 3       | NULL
3  | 1       | g

Which SQL statement do I need to run to get this:

For a given id and a given variant get the name of this combination.
But if the name is NULL get the name of the row with the given id but with variant 1 (the name of variant 1 is never NULL).
If there is no row with the given variant, again, use the row where variant is 1 with the same id.
Variant 1 is never requested directly.

This is like a fall back mechanism. Or you could consider it as overriding values of rows with variant = 1.

Examples:

id = 1, variant = 2: b
id = 1, variant = 3: c
id = 2, variant = 3: d
id = 3, variant = 5: g

Is this possible with SQL? And is it performing well if the mechanism is applied on many more fields?

Thanks!

Update:

Please note that I would like to have this mechanism not only for the name field. There should be further columns which should have the same behaviour - but each column should be treated on its own.

I think you need to add a multi column example to get a good answer on the performance bit. If you mean that each column should be treated on its own as in taken from variant x with a fallback to variant 1 if the value is null for the column my answer stands with just additional COALESCE, but that may be a bad assumption. — Joachim Isaksson, Apr 30 '13 at 14:34

score 2 · Accepted Answer · answered Apr 30 '13 at 14:25

2

This should do what you need using a LEFT JOIN to get the optional value.

SELECT COALESCE(b.Name,a.Name)
FROM Table1 a
LEFT JOIN Table1 b
  ON a.id=b.id AND b.variant=@y
WHERE a.id=@x AND a.variant=1

An SQLFiddle to test with.

Performance wise, it would depend on how you need to apply the query to get multiple fields. If you can solve your column choice using COALESCE from the existing join, I can't see a big problem, but if you end up with multiple self joins to solve it, you may have a problem.

answered Apr 30 '13 at 14:25

Joachim Isaksson

176,943
25
281
294

1

Yes, this is the solution! I tried it already with COALESCE but have got more than a single row. But you solution is right. Also with further columns. Thank you very much for the quick response! – robsch Apr 30 '13 at 14:41

score 0 · Answer 2 · answered Apr 30 '13 at 14:17

0

As always, performance will depend on your data structure, how many rows you have, and what indexes you have created. I would recommend a query like this:

SELECT name FROM table WHERE id=@1 AND ((variant=@2 AND name IS NOT NULL) OR variant=1) ORDER BY variant DESC LIMIT 1

answered Apr 30 '13 at 14:17

Dark Falcon

43,592
5
83
98

Unfortunately this solution does not work with more data columns. Joachims solution seems to be perfect. But thank you for you help. – robsch Apr 30 '13 at 14:38
It most certainly can work with more data columns if you do it correctly. It does not work with more than one record at a time though. Glad you got what you needed. – Dark Falcon Apr 30 '13 at 16:05

MySQL: Merge rows and returning default values

2 Answers2