I would like to retrieve sub categories data and its items, limiting to 3 items per subcategory
sub category 1
item1
item2
item3
sub category 2
item1
item2
item3
sub category 3
item1
item2
item3
here is my table structure
CREATE TABLE `sub_categories` (
`id` INT(11) NOT NULL,
`category_id` INT(11) NULL DEFAULT NULL,
`title` VARCHAR(255) NULL DEFAULT NULL,
`slug` VARCHAR(255) NULL DEFAULT NULL,
`active_start` DATETIME NULL DEFAULT NULL,
`active_end` DATETIME NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)]]
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
joint table
CREATE TABLE `sub_category_items` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`item_id` INT(11) NOT NULL,
`sub_category_id` INT(11) NOT NULL,
`created` DATETIME NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=64;
CREATE TABLE `items` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NULL DEFAULT NULL,
`created` DATETIME NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=64;
here is my MySQL query
SELECT `Item`.`title`,`SubCategoryItem`.`id`, `SubCategoryItem`.`item_id`, `SubCategoryItem`.`sub_category_id`, `SubCategoryItem`.`created`, `SubCategory`.`id`, `SubCategory`.`category_id`, `SubCategory`.`title`, `SubCategory`.`active_start`, `SubCategory`.`active_end`, `SubCategory`.`created`
FROM `items` AS `Item`
left JOIN `sub_category_item` AS `SubCategoryItem` ON (`SubCategoryItem`.`item_id`=`Item`.`id`) right JOIN `sub_categories` AS `SubCategory` ON (`SubCategoryItem`.`sub_category_id`=`SubCategory`.`id`)
WHERE `SubCategory`.`category_id` = 21 ORDER BY CASE
WHEN MONTH(`SubCategory`.`active_start`) = MONTH(CURDATE()) THEN 0
WHEN MONTH(`SubCategory`.`active_end`) >= MONTH(CURDATE()) THEN 1
WHEN MONTH(`SubCategory`.`active_start`) <= MONTH(CURDATE()) THEN 2
ELSE 3
END , MONTH(`SubCategory`.`active_start`) desc
what am I doing wrong ?