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.