2

I have a table named products like this:

| product_name | start_year | end_year |
|--------------|------------|----------|
| wine         | 2001       | 2012     |
| cheese       | 1999       | 2010     |
| apple        | 2008       | 2015     | 

What kind of query do I have to run to have all values between start_year and end_year where product = 'apple'?

This is the desired results:

| year |
|------|
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
0

PROPOSED QUERY

SELECT B.num year FROM
(SELECT start_year,end_year FROM products WHERE product_name='apple') A,
(SELECT tth*10000+th*1000+h*100+t*10+u num FROM
(SELECT 0 tth UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TTHN,
(SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) THN,
(SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) HN,
(SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TN,
(SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UN) B
WHERE num BETWEEN A.start_year AND A.end_year ORDER BY B.num;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS EmaOnTheBlock;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE EmaOnTheBlock;
Query OK, 1 row affected (0.00 sec)

mysql> USE EmaOnTheBlock
Database changed
mysql> CREATE TABLE products
    -> (id int not null auto_increment,
    -> product_name varchar(20),
    -> start_year smallint not null,
    -> end_year smallint not null,
    -> primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO products (product_name,start_year,end_year)
    -> VALUES ('wine',2001,2012),('cheese',1999,2010),('apple',2008,2015);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM products;
+----+--------------+------------+----------+
| id | product_name | start_year | end_year |
+----+--------------+------------+----------+
|  1 | wine         |       2001 |     2012 |
|  2 | cheese       |       1999 |     2010 |
|  3 | apple        |       2008 |     2015 |
+----+--------------+------------+----------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT B.num year FROM
    -> (SELECT start_year,end_year FROM products WHERE product_name='apple') A,
    -> (SELECT tth*10000+th*1000+h*100+t*10+u num FROM
    -> (SELECT 0 tth UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TTHN,
    -> (SELECT 0 th UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) THN,
    -> (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) HN,
    -> (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) TN,
    -> (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) UN) B
    -> WHERE num BETWEEN A.start_year AND A.end_year ORDER BY B.num;
+------+
| year |
+------+
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
+------+
8 rows in set (0.03 sec)

mysql>

GIVE IT A TRY !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.