Repeat SQL select statement until condition is met

Question

Please see image below: To get the ID's of Mazda's parent categories I would use the following:

SELECT `parent_id` FROM `product_categories` WHERE `category_id` = 8
if result is not equal to 0 repeat select statement

Is it considered bad practice to loop a SQL select statement until a condition is met? In other words; should I redesign the product categories table?

Many thanks for your advice.

product categories table screendump

score 2 · Answer 1 · answered Mar 31 '13 at 09:05

2

Yes, in this case it may be warranted to add another field that contains the top-most parent.

This of course introduces redundancy, as the information is already available in the table, but sometimes that is an acceptable tradeoff for performance.

You would add a field for the topsmost parent like this:

category_id  category_name  parent_id  top_id
------------ -------------- ---------- -------
1            CARS_          0          1
4            smartphones    0          4
5            Japanese-      1          1
7            Lexus          5          1
8            Mazda          5          1
9            Korean         1          1
10           Toyota         5          1

That can be used for getting the topmost parent for a single item, or for getting all items that belong to the same parent, which would be even more complicated with the original layout.

answered Mar 31 '13 at 09:05

Guffa

687,336
108
737
1,005

This might be the way to go for certain purposes, but it wouldn't let me build a parent_id link trail. Suppose the MAZDA product category would contain a subcategory "SUV"(11), then the link trail for this category would be 1 5 8 11. – Durian Nangka Mar 31 '13 at 09:40
@DurianNangka: No, to do that efficiently you would need another table where you would store each link trail as a number of records, where you could use the outermost leaf id to get the trail. – Guffa Mar 31 '13 at 10:22
Thanks. I'll settle 4 the extra table. – Durian Nangka Mar 31 '13 at 11:09

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

Well, using this kind of query may complicate processing the results. I strongly and personally use recursive functions instead of query loops.

more info : What is a RECURSIVE Function in PHP?

edited May 23 '17 at 10:30

Community

1
1

answered Mar 31 '13 at 09:01

Jean

524
4
21

score 0 · Answer 3 · edited Apr 13 '17 at 12:42

0

Try this (there are in-built functions for doing the same in PosgreSQL (with recursive query)

https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161

edited Apr 13 '17 at 12:42

Community

1
1

answered Mar 31 '13 at 11:45

Akash

4,956
11
42
70

Repeat SQL select statement until condition is met

3 Answers3