correct use of subqueries with mysql

I have this 3 tables in my DB: product (id, sku, type, price), product_relation (parent_id, child_id) and product_stock (product_开发者_如何学运维id, quantity, in_stock). There are possible relations between products: Product of type X can be a parent (can consists of) to several products of type Y (relation kept in product_relation table). In DB products of type X always have quantity set to 0. Now here is the thing. I need to get only products (sku and id) of type X which are in stock (in_stock = true) and at least one of their children has quantity > 0 or in_stock = true.

I'm stuck on that for several hours. I can't make a good query for this. The closest one I achieved is

SELECT a.`id`, a.`sku` FROM `product` AS a 
INNER JOIN `product_stock` AS b 
ON a.`id` = b.`product_id`
INNER JOIN `product_relation` AS c 
ON c.`child_id` = b.`product_id` 
WHERE b.`in_stock` = 1 AND a.`type` = 'X'

but it's not good, because many items are missing. I don't know if this can be handle by joins only or subqueries have Help please.

I'm not totally sure I understood what you were asking; does this help?

SELECT a.id, a.sku 
FROM product AS a 
    INNER JOIN product_stock AS b  ON a.id = b.product_id
WHERE b.in_stock = 1 AND a.type = 'X'
and exists (
    SELECT 'EXISTS'
    FROM product d
        INNER JOIN product_relation AS c ON c.child_id = d.product_id
    WHERE 
        c.product_id = a.product_id
        AND (d.quantity > 0 or d.in_stock = true)

)

You don't need a sub query if you don't want it. The advantage here is that if you wanted to know which children met the criteria you could just add its fields to the select clause.

SELECT DISTINCT parent.`id`, parent.`sku`


FROM
`product` parent as parent
INNER JOIN `product_relation` AS c 
ON c.`parent_id` = b.`product_id` 
INNER JOIN `product_stock` AS parent_stock
ON p.`product_id` = parent_stock.`product_id`    
INNER JOIN `product` as child 
ON c.`child_id` = child.`product_id`
INNER JOIN `product_stock` AS child_stock
ON c.`product_id` = child_stock.`product_id`    

WHERE parent_stock.`in_stock` = 1
    and (child_stock.`in_stock` = 1 or child_stock.`quantity`> 0)
    and  parent.`type` = 'X'