Fill in gaps in a repeated sequence in mysql

Question

I have a table that stores questions 1 - 16 and their answers given by users, and grouped by their unique id. For example:

+--------+-----------------+--------+ | number | question_number | answer | +--------+-----------------+--------+ | 1 | 1 | y | +--------+-----------------+--------+ | 1 | 2 | n | +--------+-----------------+--------+ | 1 | 3 | y | +--------+-----------------+--------+ | 2 | 1 | n | +--------+-----------------+--------+ | 2 | 3 | y | +--------+-----------------+--------+

What I want is a SELECT or an INSERT where it will fill the missing values in the sequence. So in the case on 'number' 2, it will fill in question_number 2 and insert a null value for answer. So essentially I want to fill in the gaps for the repeated sequence (in my example's case, 1-3)

so you are presented 1:1 1:2 1:3 2:1 2:3 as rows, and from that you deduce you are missing 2:2 null ? — Drew
– Drew, Commented Jul 11, 2016 at 1:23
I think you will either need a table which has the full sequence of questions or you will need some dynamic SQL. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 11, 2016 at 1:24
And if you presented 1:1 1:9 2:1 thru 2:8 and 3:1 you would deduced you are missing 1:2 thru 1:8, 2:9 and 3:2 thru 3:9 or some other unspoken VOODOO ? — Drew
– Drew, Commented Jul 11, 2016 at 1:27
Yes that's correct, and I want to fill in those rows through sql — Kristin Vernon
– Kristin Vernon, Commented Jul 11, 2016 at 2:12

Gordon Linoff · Accepted Answer · 2016-07-11 01:47:29Z

1

You can do this with a cross join and left join:

select n.number, qn.question_number, q.answer from (select distinct number from questions) n cross join (select distinct question_number from questions) qn left join questions q on q.number = n.number and q.question_number = qn.question_number;

answered Jul 11, 2016 at 1:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kristin Vernon Over a year ago

So I join with the same table?

Gordon Linoff Over a year ago

@KristinVernon . . . The cross join is to generate all the combinations for the number and question_number. The valid values are coming from the original table, so yes, this is a self-join.

Collectives™ on Stack Overflow

Fill in gaps in a repeated sequence in mysql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related