CODEWITHSUNDEEP
mysql
Klaus Turbo
Klaus Turbo

Reputation: 2960

How to use result of division in where inside MySQL query?

I've following database:

id  name prime product
1   x    2     2
2   z    3     6
3   y    17    102
4   d    5     30
5   a    7     210
6   b    11    330
7   c    13    390

Prime and product are used to determine ancestor. Now I need to find all ancestors of C which would work like this:

c.product / c.prime -> 390 / 13 = 30 --> ancestor D's product
d.product / d.prime -> 30 / 5 = 6 --> ancestor Z's product
z.product / z.prime -> 6 / 3 = 2 --> ancestor X's product
no more ancestors

Now my question is how to get the result of C and all it's ancestors within one simple MySQL query. This is the desired output:

id  name prime product
1   x    2     2
2   z    3     6
4   d    5     30
7   c    13    390

Anyone can give me a hint?

My current attempt is:

SELECT *, (product DIV prime) AS ancestor FROM temp.test_tree WHERE product = ancestor;

But that obviously doens't work.

Upvotes: 2

Views: 73

Answers (1)

mysqlrockstar
mysqlrockstar

Reputation: 2612

SELECT * FROM test_tree WHERE product IN (SELECT (product DIV prime) FROM test_tree ));

Upvotes: 1

Related Questions