SELECT s.category_id, COALESCE(s.category_value_1,s.category_value_1) AS name, e.entity_id, s.category_value_1 AS defaultName, (SELECT COUNT(*) FROM item_categories i WHERE i.subcat_id = s.unique_id) AS itemCount FROM categories c JOIN entities e ON c.parent_id = e.entity_id JOIN subcategories s ON s.parent_id = c.unique_id WHERE e.entity_value_1 = 'PRODUCTS' AND c.category_value_1 = 'ΓΛΥΚΑ' HAVING itemCount > 0 ORDER BY s.cat_order ASC;
Array ( [0] => Array ( [0] => frm_subcat_5 [category_id] => frm_subcat_5 [1] => Τούρτες [name] => Τούρτες [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Τούρτες [defaultName] => Τούρτες [4] => 34 [itemCount] => 34 ) [1] => Array ( [0] => frm_subcat_16 [category_id] => frm_subcat_16 [1] => Τούρτες Μίνι [name] => Τούρτες Μίνι [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Τούρτες Μίνι [defaultName] => Τούρτες Μίνι [4] => 11 [itemCount] => 11 ) [2] => Array ( [0] => frm_subcat_13 [category_id] => frm_subcat_13 [1] => Μπολ Ατομικά [name] => Μπολ Ατομικά [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Μπολ Ατομικά [defaultName] => Μπολ Ατομικά [4] => 8 [itemCount] => 8 ) [3] => Array ( [0] => frm_subcat_15 [category_id] => frm_subcat_15 [1] => Πάστες [name] => Πάστες [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Πάστες [defaultName] => Πάστες [4] => 40 [itemCount] => 40 ) [4] => Array ( [0] => frm_subcat_7 [category_id] => frm_subcat_7 [1] => Σιροπιαστά [name] => Σιροπιαστά [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Σιροπιαστά [defaultName] => Σιροπιαστά [4] => 17 [itemCount] => 17 ) [5] => Array ( [0] => frm_subcat_6 [category_id] => frm_subcat_6 [1] => Γλυκά - Καφέ [name] => Γλυκά - Καφέ [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Γλυκά - Καφέ [defaultName] => Γλυκά - Καφέ [4] => 13 [itemCount] => 13 ) [6] => Array ( [0] => frm_subcat_11 [category_id] => frm_subcat_11 [1] => Κεράσματα [name] => Κεράσματα [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Κεράσματα [defaultName] => Κεράσματα [4] => 13 [itemCount] => 13 ) [7] => Array ( [0] => frm_subcat_9 [category_id] => frm_subcat_9 [1] => Σφηνάκια [name] => Σφηνάκια [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Σφηνάκια [defaultName] => Σφηνάκια [4] => 8 [itemCount] => 8 ) [8] => Array ( [0] => frm_subcat_8 [category_id] => frm_subcat_8 [1] => Μινιατούρες [name] => Μινιατούρες [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Μινιατούρες [defaultName] => Μινιατούρες [4] => 25 [itemCount] => 25 ) [9] => Array ( [0] => frm_subcat_14 [category_id] => frm_subcat_14 [1] => Πλάκες Ξενοδοχείου [name] => Πλάκες Ξενοδοχείου [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Πλάκες Ξενοδοχείου [defaultName] => Πλάκες Ξενοδοχείου [4] => 5 [itemCount] => 5 ) )

SELECT s.category_id, COALESCE(s.category_value_1,s.category_value_1) AS name, e.entity_id, s.category_value_1 AS defaultName, (SELECT COUNT(*) FROM item_categories i WHERE i.subcat_id = s.unique_id) AS itemCount FROM categories c JOIN entities e ON c.parent_id = e.entity_id JOIN subcategories s ON s.parent_id = c.unique_id WHERE e.entity_value_1 = 'PRODUCTS' AND c.category_value_1 = 'ΓΛΥΚΑ ΣΤΕΒΙΑ' HAVING itemCount > 0 ORDER BY s.cat_order ASC;
Array ( [0] => Array ( [0] => frm_subcat_2 [category_id] => frm_subcat_2 [1] => Τούρτες [name] => Τούρτες [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Τούρτες [defaultName] => Τούρτες [4] => 3 [itemCount] => 3 ) [1] => Array ( [0] => frm_subcat_1 [category_id] => frm_subcat_1 [1] => Πάστες [name] => Πάστες [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Πάστες [defaultName] => Πάστες [4] => 4 [itemCount] => 4 ) )

SELECT s.category_id, COALESCE(s.category_value_1,s.category_value_1) AS name, e.entity_id, s.category_value_1 AS defaultName, (SELECT COUNT(*) FROM item_categories i WHERE i.subcat_id = s.unique_id) AS itemCount FROM categories c JOIN entities e ON c.parent_id = e.entity_id JOIN subcategories s ON s.parent_id = c.unique_id WHERE e.entity_value_1 = 'PRODUCTS' AND c.category_value_1 = 'ΑΛΜΥΡΕΣ ΚΑΙ ΓΛΥΚΕΣ ΝΟΣΤΙΜΙΕΣ' HAVING itemCount > 0 ORDER BY s.cat_order ASC;
Array ( [0] => Array ( [0] => frm_subcat_1 [category_id] => frm_subcat_1 [1] => Γλυκές λιχουδιές [name] => Γλυκές λιχουδιές [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Γλυκές λιχουδιές [defaultName] => Γλυκές λιχουδιές [4] => 5 [itemCount] => 5 ) [1] => Array ( [0] => frm_subcat_2 [category_id] => frm_subcat_2 [1] => Αλμυρές λιχουδιές [name] => Αλμυρές λιχουδιές [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Αλμυρές λιχουδιές [defaultName] => Αλμυρές λιχουδιές [4] => 19 [itemCount] => 19 ) )

SELECT s.category_id, COALESCE(s.category_value_1,s.category_value_1) AS name, e.entity_id, s.category_value_1 AS defaultName, (SELECT COUNT(*) FROM item_categories i WHERE i.subcat_id = s.unique_id) AS itemCount FROM categories c JOIN entities e ON c.parent_id = e.entity_id JOIN subcategories s ON s.parent_id = c.unique_id WHERE e.entity_value_1 = 'PRODUCTS' AND c.category_value_1 = 'ΠΑΓΩΤΑ' HAVING itemCount > 0 ORDER BY s.cat_order ASC;
Array ( [0] => Array ( [0] => frm_subcat_6 [category_id] => frm_subcat_6 [1] => Τούρτα - Παγωτό [name] => Τούρτα - Παγωτό [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Τούρτα - Παγωτό [defaultName] => Τούρτα - Παγωτό [4] => 19 [itemCount] => 19 ) [1] => Array ( [0] => frm_subcat_10 [category_id] => frm_subcat_10 [1] => Τούρτα - Παγωτό Miss Gelo [name] => Τούρτα - Παγωτό Miss Gelo [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Τούρτα - Παγωτό Miss Gelo [defaultName] => Τούρτα - Παγωτό Miss Gelo [4] => 12 [itemCount] => 12 ) [2] => Array ( [0] => frm_subcat_9 [category_id] => frm_subcat_9 [1] => Παγωτίνια Χειροποίητα [name] => Παγωτίνια Χειροποίητα [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Παγωτίνια Χειροποίητα [defaultName] => Παγωτίνια Χειροποίητα [4] => 9 [itemCount] => 9 ) [3] => Array ( [0] => frm_subcat_7 [category_id] => frm_subcat_7 [1] => Παγωτίνια Χωνάκι [name] => Παγωτίνια Χωνάκι [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Παγωτίνια Χωνάκι [defaultName] => Παγωτίνια Χωνάκι [4] => 5 [itemCount] => 5 ) [4] => Array ( [0] => frm_subcat_8 [category_id] => frm_subcat_8 [1] => Παγωτίνια Ξυλάκι [name] => Παγωτίνια Ξυλάκι [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Παγωτίνια Ξυλάκι [defaultName] => Παγωτίνια Ξυλάκι [4] => 13 [itemCount] => 13 ) [5] => Array ( [0] => frm_subcat_11 [category_id] => frm_subcat_11 [1] => Σπιτικό Παγωτό [name] => Σπιτικό Παγωτό [2] => frm_entity_9 [entity_id] => frm_entity_9 [3] => Σπιτικό Παγωτό [defaultName] => Σπιτικό Παγωτό [4] => 15 [itemCount] => 15 ) )