i have to extract from the db the product that has parent id from an order that could have some parent/child products (usually a kit). I wrote the following query, and it’s working fine, but only there is one parent. The problem is that if there are more parents i can’t extract them all because i get the subquery error about returning more than one row.

query:

SELECT product_id, parent_child_value, parent_id, parent_type, 
  CASE WHEN parent_type != ' ' THEN (SELECT product_id FROM products_table
        WHERE parent_child_value = (SELECT DISTINCT(parent_id)
                FROM products_table
                WHERE id = 00001
                AND code ='01'
                AND type ='AT'
                AND parent_id > 0)
   AND id = 00001
   AND code ='01'
   AND type ='AT')
   ELSE null
   END AS parent
 FROM products_table
 WHERE id = 00001
 AND code ='01'
 AND type ='AT'

How could i do for extract it for each order that could have or could not a parent or even have more than one parent? I could try to work it in laravel code, but if there is a way to do it with one or two queries would be better. Any ideas how could it be?