Anyone got any bright ideas of how to solve this one?

I have documents which can contain up to 15 rows of information.
Each row as it is added to the document reference can have any ROW_ID
from 1 to 15 when it is added.

I want to be able to "Auto Fill" the blank rows on a SELECT.

eg

data in table is

ROW_ID | Reference
===================================
1 | Reference Line 1
3 | Reference Line 3
9 | Reference Line 9
11 | Reference Line 11
15 | Reference Line 15

RESULT REQUIRED is

ROW_ID | Reference
===================================
1 | Reference Line 1
2 |
3 | Reference Line 3
4 |
5 |
6 |
7 |
8 |
9 | Reference Line 9
10 |
11 | Reference Line 11
12 |
13 |
14 |
15 | Reference Line 15

I've been playing about with joins on a "dummy" table containing just
rows 1 to 15, but am stuck in my thinking at the moment.

Any gratefully received

Roger

you could do something like

select dummy.row_id,real.reference from dummy left join real on
real.row_id=dummy.row_id;

would give NULL on the 'missing' rows,

On Wed, Mar 12, 2008 at 12:50 PM, roger.maynard <
roger.maynard@crayford.ltd.uk> wrote:

> Anyone got any bright ideas of how to solve this one?
>
> I have documents which can contain up to 15 rows of information.
> Each row as it is added to the document reference can have any ROW_ID
> from 1 to 15 when it is added.
>
> I want to be able to "Auto Fill" the blank rows on a SELECT.
>
> eg
>
> data in table is
>
> ROW_ID | Reference
> ===================================
> 1 | Reference Line 1
> 3 | Reference Line 3
> 9 | Reference Line 9
> 11 | Reference Line 11
> 15 | Reference Line 15
>
> RESULT REQUIRED is
>
> ROW_ID | Reference
> ===================================
> 1 | Reference Line 1
> 2 |
> 3 | Reference Line 3
> 4 |
> 5 |
> 6 |
> 7 |
> 8 |
> 9 | Reference Line 9
> 10 |
> 11 | Reference Line 11
> 12 |
> 13 |
> 14 |
> 15 | Reference Line 15
>
> I've been playing about with joins on a "dummy" table containing just
> rows 1 to 15, but am stuck in my thinking at the moment.
>
> Any gratefully received
>
> Roger
>



--
build our city at http://free-dc.myminicity.com !

To get a blank line instead of NULL you could simply wrap the reference
in an IFNULL function:

select dummy.row_id,IFNULL(real.reference,'') as Reference from dummy left join real on
real.row_id=dummy.row_id;





Phil wrote:
> you could do something like
>
> select dummy.row_id,real.reference from dummy left join real on
> real.row_id=dummy.row_id;
>
> would give NULL on the 'missing' rows,
>
> On Wed, Mar 12, 2008 at 12:50 PM, roger.maynard <
> roger.maynard@crayford.ltd.uk> wrote:
>
>
>> Anyone got any bright ideas of how to solve this one?
>>
>> I have documents which can contain up to 15 rows of information.
>> Each row as it is added to the document reference can have any ROW_ID
>> from 1 to 15 when it is added.
>>
>> I want to be able to "Auto Fill" the blank rows on a SELECT.
>>
>> eg
>>
>> data in table is
>>
>> ROW_ID | Reference
>> ===================================
>> 1 | Reference Line 1
>> 3 | Reference Line 3
>> 9 | Reference Line 9
>> 11 | Reference Line 11
>> 15 | Reference Line 15
>>
>> RESULT REQUIRED is
>>
>> ROW_ID | Reference
>> ===================================
>> 1 | Reference Line 1
>> 2 |
>> 3 | Reference Line 3
>> 4 |
>> 5 |
>> 6 |
>> 7 |
>> 8 |
>> 9 | Reference Line 9
>> 10 |
>> 11 | Reference Line 11
>> 12 |
>> 13 |
>> 14 |
>> 15 | Reference Line 15
>>
>> I've been playing about with joins on a "dummy" table containing just
>> rows 1 to 15, but am stuck in my thinking at the moment.
>>
>> Any gratefully received
>>
>> Roger
>>
>>
>
>
>
>