1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
GROUP_CONCAT(`category_prep`.`Category` ORDER BY `sort`) as category
FROM (SELECT (IF (EXISTS (SELECT 1 FROM preparat INNER JOIN ((tovar INNER JOIN stock ON tovar.id = stock.idTovar) INNER JOIN apteki a ON stock.idApteki = a.id) ON preparat.id = tovar.idPreparat WHERE preparat.Prep_s Like '%крем%'
AND `a`.`idCity`=1 AND stock.idApteki<>65 ), 1,0)) as cat1
UNION
SELECT (IF (EXISTS (SELECT 1 FROM ((preparat_nm INNER JOIN tovar ON preparat_nm.idPreparat_nm = tovar.id) INNER JOIN stock ON tovar.id = stock.idTovar) INNER JOIN apteki a ON stock.idApteki = a.id WHERE preparat_nm.Prep_nm Like '%крем%'
AND `a`.`idCity`=1 AND stock.idApteki<>65 AND preparat_nm.Category_nm=2 ), 2,0)) as cat2
UNION
SELECT (IF (EXISTS (SELECT 1 FROM ((preparat_nm INNER JOIN tovar ON preparat_nm.idPreparat_nm = tovar.id) INNER JOIN stock ON tovar.id = stock.idTovar) INNER JOIN apteki a ON stock.idApteki = a.id WHERE preparat_nm.Prep_nm Like '%крем%'
AND `a`.`idCity`=1 AND stock.idApteki<>65 AND preparat_nm.Category_nm=3 ), 3,0)) as cat3
UNION
SELECT (IF (EXISTS (SELECT 1 FROM ((preparat_nm INNER JOIN tovar ON preparat_nm.idPreparat_nm = tovar.id) INNER JOIN stock ON tovar.id = stock.idTovar) INNER JOIN apteki a ON stock.idApteki = a.id WHERE preparat_nm.Prep_nm Like '%крем%'
AND `a`.`idCity`=1 AND stock.idApteki<>65 AND preparat_nm.Category_nm=4 ), 4,0)) as cat4
UNION
SELECT (IF (EXISTS (SELECT 1 FROM ((preparat_nm INNER JOIN tovar ON preparat_nm.idPreparat_nm = tovar.id) INNER JOIN stock ON tovar.id = stock.idTovar) INNER JOIN apteki a ON stock.idApteki = a.id WHERE preparat_nm.Prep_nm Like '%крем%'
AND `a`.`idCity`=1 AND stock.idApteki<>65 AND preparat_nm.Category_nm=5 ), 5,0)) as cat5) as cat INNER JOIN category_prep ON cat.cat1 = category_prep.id
я при помощи Exists (самый быстрый вариант из мною тестированных в данном случае) нашел категории, где есть препарат
и присвоил данным столбца id категорий, потом сделал при помощи union один столбец, который c JOIN-ил с таблицей категорий и вывел название категории
теперь мне нужно посчитать кол-во препаратов в каждой категории, у кого есть какие идеи?
