0

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 ?

Devin
  • 7,690
  • 6
  • 39
  • 54

0 Answers0