MYSQL: Returning rows for each number column

on

Howdy!

Its been a while since my last post - over a month !

I did not infact forget I had a blog, rather I have written several posts to near completion and have got caught up with some larger Angular projects that I will talk about in the future !!
It was also , yuno , Christmas so I took a bit of a break.

Today I explain how to create an SQL query that will return a number of rows based on the value of a column - Eg. A Qty column with a value of 3 will have rows returned.

This is moreso for myself as each time I need to do this I have forgotten how I did the previous times and at least now I will have a record here !!

Why Do I Need To Do This ?

This can be extremely useful , for example , if you needed to return 2 rows of data for a product when printing a Barcode and you needed 1 label for each Box that the product has.

Getting Started

I will use MYSQL & MYSQL Workbench which are free to use. You can use any Database or the default Sakilla one, but we will reate our own tables and data for this post.

Creating the Seed Data And Tables

Below you will find Table Create Statement and the Seed Data. These are simple enough , and you can replace these with your own if you want. 


CREATE TABLE `order_item` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(45) NULL,
  `qty` INT NULL,
  PRIMARY KEY (`id`)); 

INSERT INTO `order_item` (`product_name`, `qty`) VALUES ('Bluray Player', '3');
INSERT INTO `order_item` (`product_name`, `qty`) VALUES ('Freeview Antena ', '2');
INSERT INTO `order_item` (`product_name`, `qty`) VALUES ('Philips 42 TV ', '1');
INSERT INTO `order_item` (`product_name`, `qty`) VALUES ('TV Stand', '4');
INSERT INTO `order_item` (`product_name`, `qty`) VALUES ('HDMI Cable', '2');

Creating Our Numbers Table

The core of the logic comes from joining the order_item table to a numbers table. We need to create a numbers table called Tally.
You could populate the Tally table manually but this would be time consuming so I have created a Stored Procedure that will insert a new entry up to the given number eg 100 will create entries 1 - 100 in Tally. I have used a Stored Procedure because it allows me to use a LOOP which is the essential here. 


CREATE TABLE  `tally` (
  `number` INT NULL);


DELIMITER $$
DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    INSERT INTO tally (number) value(int_val); 
  END LOOP; 
END$$
DELIMITER ;
/* Make Sure To Call the Procedure !! */
CALL proc_loop_test() ;

Selecting the Data

Now all we need to do is join the Order_item table to the Tally table on Tally.Number <= the Order_item.Qty. Its pretty simple. 


select * 
from order_item o
inner join tally t on t.number <= o.qty
order by o.product_name;

Results

This query will return the results we need. You can see this in the image below.

Comments

Post a Comment