Thank you @wake689. I am not really sure I am approaching the problem in a wise way, as you said.
This database comes from an ERP that I have minimal control over. Its content must be plugged to a system that expects an associative array (php) of products and its variants.
At first I thought of doing a query to get the distinct ‘main’ products, and then query each one in a loop to get its variants. Then I learned that queries in loops are a bad idea.
My problem in short is how to build this array of products and variants from the flat database rows.