ID | priceID | parentID | value | ------|-----------|------------|---------| 1 | 1 | | 0.01 | 2 | 1 | | 0.02 | 3 | 2 | 1 | 0.11 | 4 | 2 | 1 | 0.12 |

It is necessary to substitute the "descendant" all the values ​​of "parent". Here's how I'm doing now:

 SELECT DISTINCT t1.priceID, IFNULL(t2.value, t1.value) value FROM some_table t1 LEFT JOIN some_table t2 on t1.parentID = t2.priceID WHERE t1.priceID = 2 priceID | value | -----------|---------| 2 | 0.01 | 2 | 0.02 | 2 | 0.11 | 2 | 0.12 |

This method is working, but in fact I have quite a lot of fields, and for everyone I don't want to write IFNULL (). Is there any other way to solve my problem? If this is significant, I want to make Views from this request, which certainly imposes its own limitations (for example, I already know that you need to avoid subqueries when writing queries for views). The solution I mentioned above behaves very slowly in the view.

  • It is necessary to write for each IFNULL () - igaraev
  • @toxxxa You can try select priceID, value from some_table where priceID is in (select parentID from some_table where priceID = 2 and parentID is not null) union select parentID. value from some_table where priceID = 2 and parentID is null Not applicable everywhere and I don’t know whether it will be faster - Mike
  • @Mike, in fact, can be even simpler without union : select priceID, value from some_table where priceID in (2, (select parentID from some_table where priceID=2)) , but you cannot make views from this (subquery, also with a condition ). - toxxxa
  • @toxxxa In general, for MySQL, it is said that the view cannot use subqueries only in the FROM part of the main query. so in WHERE you can probably check it out. - Mike
  • @toxxxa Although yes, you will not pass the value 2 into such a view. Then it seems that the only solution in question is Mike

0