How to iterate over joined data in two or more nested loops?

Question

Some time ago I asked a question about nested loops on SO and as it was, there were queries inside the loops of my example and I got a clear answer:

NEVER EVER NEVER put an SQL query inside a loop

I've tried ever since and mostly it works. Just need to make an effort and write a query that retrieves all you need at once.

BUT what do you do when you have a dataset from a JOIN query which contains nested data which you need to output in a nested way?

Example join from table A and B:

A.a     |  B.a     |  B.b
--------|----------|-------------
fruits  |  banana  |  yellow
fruits  |  apple   |  red
animals |  zebra   |  black&white
animals |  elefant |  gray
animals |  fox     |  red
planets |  earth   |  blue
planets |  mars    |  red

ok, now I got that all in an array or rowset and now I need to display something like that:

fruits

• yellow banana
• red apple

animals

• black&white zebra
• gray elefant
• red fox

planets

• blue earth
• red mars

it seems obvious that it should work but I've tried to wrap my mind around it several times now and I just can't come up with a solution.

At the moment I do it my old way:

query groups

foreach groups
{
    query animals in group
    foreach animal
}

but hey, NEVER EVER NEVER put sql inside a loop. so what shold I do? I do PHP but I think this is a meta question.

Answer 1

If what you have is a hierarchy, a "directed acyclic graph". SQL does not do these.

There are other graph-theory things SQL does not do.

Since SQL does not do this, the "never put SQL in a loop" rule goes out the window.

You must put the SQL in a loop for hierarchies and other graph-connection problems involving lattices and networks.

Indeed, for hierarchies, you must use recursive loops to connect all elements of the hierarchy to arbitrary depth.

If, on the other hand, you're just reformatting the query result to look like a nested hierarchy, then you're just reformatting a single SQL result set into what appears to be nested lists.

This will be one select with complex loops around the result set. One select -- not in a loop -- and a complex loop to process one result set.

Answer 2

Use the control break algorithm.

I'd return a result set exactly as you show in the question:

A.a     |  B.a     |  B.b
--------|----------|-------------
fruits  |  banana  |  yellow
fruits  |  apple   |  red
animals |  zebra   |  black&white
animals |  elefant |  gray
animals |  fox     |  red
planets |  earth   |  blue
planets |  mars    |  red

loop over all the rows:

• when A.a changes, output the title
• then always output the B.b + B.a value

pseudo code for application calling SQL:

set last_A = null
exec query

loop over result set {
    if last_A == null or fetch_A!=last_A {
        last_A=fetch_A
        display fetch_a
    }
    display fetch_Bb + fetch_Ba
    }
}//loop