Select n rows of a category from a table where categories are defined in another table

Question

I have a offers table which has items from item table configured as offers. Items in this table are repeated with different prices.

In the items table, there are items which are of different categories. There is a separate category table which is referenced by the items table by foreign key relation.

Now for my specific purpose, I want to select 2 items of each category from the offers table the items can be repeated but not more than 2 per category. I also want to perform these tasks in a single query or stored procedure.

I have tried the following https://stackoverflow.com/a/6732697/4742713 which requires me to use unions and multiple select statements, each time changing the category id, but I don't wish to use unions as categories may be added in future.

I have also tried this solution https://stackoverflow.com/a/1092038/4742713 which uses mysql variables as counters but it is not consistent and has repeated rows (more than desired) of same categories. Below is my table if it helps

CREATE TABLE `itemcat` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `itemcat`
  ADD UNIQUE `name` (`name`);

INSERT INTO `itemcat` (`id`, `name`) VALUES
(1, 'C1'),
(2, 'C2'),
(3, 'C3'),
(4, 'C4'),
(5, 'C5');


CREATE TABLE `item` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `cat_id` int(11) DEFAULT '1',
  `name` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `item`
  ADD KEY `item_fk0` (`cat_id`);

ALTER TABLE `item`
  ADD CONSTRAINT `item_fk0` FOREIGN KEY (`cat_id`) REFERENCES `itemcat` (`id`);

INSERT INTO `item` (`cat_id`, `name`) VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E'),
(1, 'F'),
(2, 'G'),
(3, 'H'),
(4, 'I'),
(5, 'J'),
(1, 'K'),
(2, 'L'),
(3, 'M'),
(4, 'N'),
(5, 'O'),
(1, 'P'),
(2, 'Q'),
(3, 'R'),
(4, 'S'),
(5, 'T'),
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E'),
(1, 'F'),
(2, 'G'),
(3, 'H'),
(4, 'I'),
(5, 'J'),
(1, 'K'),
(2, 'L'),
(3, 'M'),
(4, 'N'),
(5, 'O'),
(1, 'P'),
(2, 'Q'),
(3, 'R'),
(4, 'S'),
(5, 'T');
CREATE TABLE `offer` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `regular_price` decimal(10,2) DEFAULT NULL,
  `offer_price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `offer` (`item_id`, `regular_price`, `offer_price`) VALUES
( 1, '499.00', '199.00'),
( 2, '299.00', '149.00'),
( 3, '599.00', '199.00'),
( 4, '999.00', '399.00'),
( 5, '199.00', '99.00'),
( 5, '199.00', '79.00'),
( 6, '299.00', '199.00'),
( 6, '399.00', '199.00'),
( 6, '1099.00', '499.00'),
( 6, '3590.00', '1299.00'),
( 7, '15900.00', '12990.00'),
( 7, '20990.00', '12990.00'),
( 8, '35999.00', '19990.00'),
( 8, '110.00', '85.00'),
( 9, '10990.00', '7990.00'),
( 9, '510.00', '329.00'),
( 9, '35.00', '12.00'),
( 9, '60.00', '35.00'),
( 10, '149.00', '69.00'),
( 10, '168.00', '119.00'),
( 10, '358.00', '139.00'),
( 10, '514.00', '438.00'),
( 12, '158.00', '133.00'),
( 12, '152.00', '127.00'),
( 12, '42.00', '36.00'),
( 12, '25.00', '21.00'),
( 12, '335.00', '281.00'),
( 1, '421.00', '359.00'),
( 2, '299.00', '149.00'),
( 1, '499.00', '199.00'),
( 2, '499.00', '199.00'),
( 4, '299.00', '149.00'),
( 3, '599.00', '199.00'),
( 5, '999.00', '399.00'),
( 5, '199.00', '99.00'),
( 7, '199.00', '79.00'),
( 6, '299.00', '199.00'),
( 1, '399.00', '199.00'),
( 2, '1099.00', '499.00'),
( 6, '3590.00', '1299.00'),
( 8, '15900.00', '12990.00'),
( 9, '20990.00', '12990.00'),
( 5, '35999.00', '19990.00'),
( 1, '110.00', '85.00'),
( 7, '10990.00', '7990.00'),
( 2, '510.00', '329.00'),
( 3, '35.00', '12.00'),
( 4, '60.00', '35.00'),
( 15, '149.00', '69.00'),
( 16, '168.00', '119.00'),
( 17, '358.00', '139.00'),
( 6, '158.00', '133.00'),
( 18, '152.00', '127.00'),
( 19, '42.00', '36.00'),
( 12, '25.00', '21.00'),
( 12, '180.00', '139.00'),
( 12, '139.00', '99.00'),
( 12, '301.00', '89.00'),
( 12, '199.00', '109.00'),
( 12, '350.00', '179.00'),
( 15, '299.00', '149.00'),
( 13, '999.00', '349.00'),
( 13, '145.00', '120.00'),
( 15, '108.00', '88.00'),
( 15, '485.00', '335.00'),
( 13, '240.00', '199.00'),
( 13, '199.00', '172.00'),
( 15, '930.00', '599.00'),
( 13, '399.00', '289.00'),
( 8, '99.00', '49.00'),
( 15, '60.00', '40.00'),
( 15, '125.00', '67.00'),
( 22, '864.00', '449.00'),
( 13, NULL, '249.00'),
( 3, '514.00', '438.00'),
( 12, '550.00', '369.00'),
( 11, '205.00', '175.00'),
( 11, NULL, '58.00'),
( 11, '1200.00', '799.00'),
( 11, '1200.00', '849.00'),
( 15, '396.00', '350.00'),
( 11, '1200.00', '849.00'),
( 11, '1550.00', '1349.00'),
( 11, '335.00', '281.00'),
( 11, '421.00', '359.00'),
( 12, '299.00', '149.00'),
( 1, '499.00', '199.00'),
( 2, '499.00', '199.00'),
( 2, '299.00', '149.00'),
( 3, '599.00', '199.00'),
( 5, '999.00', '399.00'),
( 6, '199.00', '99.00'),
( 7, '199.00', '79.00'),
( 10, '299.00', '199.00'),
( 1, '399.00', '199.00'),
( 2, '1099.00', '499.00'),
( 6, '3590.00', '1299.00'),
( 8, '15900.00', '12990.00'),
( 9, '20990.00', '12990.00'),
( 5, '35999.00', '19990.00'),
( 11, '110.00', '85.00'),
( 7, '10990.00', '7990.00'),
( 12, '510.00', '329.00'),
( 13, '35.00', '12.00'),
( 14, '60.00', '35.00'),
( 15, '149.00', '69.00'),
( 16, '168.00', '119.00'),
( 17, '358.00', '139.00'),
( 11, NULL, '58.00');

ALTER TABLE `offer`
  ADD KEY `offer_fk1` (`item_id`);


ALTER TABLE `offer`
  ADD CONSTRAINT `offer_fk1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE;
COMMIT;

Now, for the above Schema, I want to run

select i.name as item_name, ic.name as cat_name, o.regular_price, o.offer_price from offer o, item i, itemcat ic where i.cat_id=ic.id and o.item_id=i.id limit 30

From the output of this table i want to choose 2 items of each category

Sorry for the confusion earlier.

Thanks Guys!

`I want to select 2 items of each category from the items table without repetition of categories.` You state two different requirements, which are contradicting. 1.) `I want to select 2 items of each category from the items table` tells us you want to select `2*n` items where `n` is the number of categories. 2.) `without repetition of categories.` You want each catagory to appear only once, which contradict with the requirement that two items should be selected **per category**. So which one do you want to have? — Tobias F., Feb 20 '18 at 09:27
2 items of a category thats what i need. I will edit the question — Rohit Agre, Feb 20 '18 at 09:28
@cdaiga I have made the changes. Sorry for the earlier confusion — Rohit Agre, Feb 20 '18 at 11:06
okay, so i have narrowed down the problem, I am using php pdo to run these commands and for some reason `SET @counter = 0,@category = '';` these statements are not running. when i am using this (https://stackoverflow.com/a/1092038/4742713) solution — Rohit Agre, Feb 20 '18 at 11:53

Select n rows of a category from a table where categories are defined in another table

0 Answers0