Web lists-archives.org

Re: Auto Fill blank Rows

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@xxxxxxxxxxxxxxx> 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 help gratefully received

Roger