SQL get parent item id in for each product_id

Issue

This Content is from Stack Overflow. Question asked by shaddark

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?



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?