Consider this table in mysql
create table numbers (number int);
insert into numbers values (3), (2), (9);
select * from numbers;
+--------+
| number |
+--------+
| 3 |
| 2 |
| 9 |
+--------+
Is there a simple query to generate a table with the following columns
- The numbers from 1 till 10
- 1 if the number is present in the table numbers, and 0 otherwise
I guess you have to create a sequence of numbers in order to do this. If possible, I want to create such a sequence without storing it in the database.
Related question: is there a select query that generates a sequence of numbers from 1 till 10 (or 100 or 1000)?
Best Answer
IN MariaDB
MariaDB has a SEQUENCE Storage Engine. So, if you are using MariaDB, all your sequence troubles are over (or have just begun).
Sequence of 10 numbers
NOT USING TABLES
Sequence of 10 numbers
Sequence of 100 numbers
Sequence of 1000 numbers
Sequence of 10000 numbers
USING TABLES
Sequence of 10 numbers
Sequence of 100 numbers
Sequence of 1000 numbers
Sequence of whatever numbers (Example: 3.5 million)
YOUR ACTUAL QUESTION
Using the sequence
0..9
, you left join the sequence against the tableYour sample data with the new query
GIVE IT A TRY !!!