Cześć,
Mam zagwozdkę z multizapytaniem mysql.

W skrócie chcę wyświetlać listę produktów, których podprodukty mają stan magazynowy minium 1.


Przykładowa tabela:
products

id | product_id
1 | null
2 | 1
3 | 1
4 | null
5 | 4
6 | 4
7 | null
8 | 7
9 | 7

stock:
id | product_id | amount
1 | 2 | 0
2 | 3 | 1
3 | 5 | 2
4 | 6 | 3
5 | 8 | 0
6 | 9 | 0


Chciałbym wyciągnąć z tej tabeli wyświetlanie głównych produktów, których warianty mają amount > 0


Aktualnie zapytanie wygląda tak:
Brakuje tylko wywietlania produktów, których stany magazynowe wariantów będą na minimum 1

[SQL] pobierz, plaintext 
SELECT `products`.*
FROM `products`
WHERE ((`category_id`=23) OR (`add_category_hash` LIKE '%23%') OR (`category_id`=57) OR (`add_category_hash` LIKE '%57%') OR (`category_id`=58) OR (`add_category_hash` LIKE '%58%') OR (`category_id`=59) OR (`add_category_hash` LIKE '%59%') OR (`category_id`=60) OR (`add_category_hash` LIKE '%60%') OR (`category_id`=61) OR (`add_category_hash` LIKE '%61%') OR (`category_id`=62) OR (`add_category_hash` LIKE '%62%') OR (`category_id`=63) OR (`add_category_hash` LIKE '%63%') OR (`category_id`=64) OR (`add_category_hash` LIKE '%64%') OR (`category_id`=65) OR (`add_category_hash` LIKE '%65%') OR (`category_id`=66) OR (`add_category_hash` LIKE '%66%') OR (`category_id`=67) OR (`add_category_hash` LIKE '%67%') OR (`category_id`=68) OR (`add_category_hash` LIKE '%68%')) AND (`products`.`is_active` = 1) AND (`products`.`parent_id` IS NULL) AND (`products`.`price_netto` BETWEEN 0 AND 99999) AND (`products`.`price_brutto` BETWEEN 0 AND 99999) AND ((`products`.`id` IN (
SELECT `parent_id`
FROM `products`
WHERE `products`.`id` IN (
SELECT `product_id`
FROM `variants_hash`
WHERE (`variant_id` = '104') AND (`variants_hash`.`product_id` IN (
SELECT `product_id`
FROM `bwcms_xmod_shop_stocks`
WHERE (`variant_id` = '104') AND (`amount` > 0)))))) OR (`products`.`id` IN (
SELECT `product_id`
FROM `products_variants`
WHERE `variant_id` = '104')))
ORDER BY `id`
LIMIT 16
[SQL] pobierz, plaintext 

Problem rozwiązany, może komuś się przyda.
Jako SELECT w głównym zapytaniu napisałem podzapytanie, które sumuje warianty i wyświetla daną ilość dla całej grupy.
Chyba, że można zrobić prościej to chętnie o tym porozmawiam

[SQL] pobierz, plaintext 
SELECT `products_list`.*,(
SELECT SUM(amount)
FROM `stocks` s
WHERE `product_id` IN (
SELECT `id`
FROM `products_list` `list`
WHERE (`is_active`=1) AND (`parent_id`=`products_list`.id))) AS `amount`
FROM `products_list`
WHERE ((`category_id`=23) OR (`add_category_hash` LIKE '%23%') OR (`category_id`=57) OR (`add_category_hash` LIKE '%57%') OR (`category_id`=58) OR (`add_category_hash` LIKE '%58%') OR (`category_id`=59) OR (`add_category_hash` LIKE '%59%') OR (`category_id`=60) OR (`add_category_hash` LIKE '%60%') OR (`category_id`=61) OR (`add_category_hash` LIKE '%61%') OR (`category_id`=62) OR (`add_category_hash` LIKE '%62%') OR (`category_id`=63) OR (`add_category_hash` LIKE '%63%') OR (`category_id`=64) OR (`add_category_hash` LIKE '%64%') OR (`category_id`=65) OR (`add_category_hash` LIKE '%65%') OR (`category_id`=66) OR (`add_category_hash` LIKE '%66%') OR (`category_id`=67) OR (`add_category_hash` LIKE '%67%') OR (`category_id`=68) OR (`add_category_hash` LIKE '%68%')) AND (`products_list`.`is_active` = 1) AND (`products_list`.`parent_id` IS NULL) AND (`products_list`.`price_netto` BETWEEN 0 AND 99999) AND (`products_list`.`price_brutto` BETWEEN 0 AND 99999)
HAVING `amount` > 0
ORDER BY `id`
LIMIT 16
[SQL] pobierz, plaintext