Now I come to exapnd the requirement it not correct...
to elaborate..
I create tables as such...
DROP TABLE IF EXISTS filler;
CREATE TABLE filler (row_id int);
INSERT INTO filler (row_id) VALUES (1);
INSERT INTO filler (row_id) VALUES (2);
INSERT INTO filler (row_id) VALUES (3);
INSERT INTO filler (row_id) VALUES (4);
INSERT INTO filler (row_id) VALUES (5);
INSERT INTO filler (row_id) VALUES (6);
INSERT INTO filler (row_id) VALUES (7);
INSERT INTO filler (row_id) VALUES (8);
INSERT INTO filler (row_id) VALUES (9);
INSERT INTO filler (row_id) VALUES (10);
DROP TABLE IF EXISTS original;
CREATE TABLE original (row_id int,reference varchar(15));
INSERT INTO original (row_id,reference) VALUES (1,"Reference 1");
INSERT INTO original (row_id,reference) VALUES (3,"Reference 3");
INSERT INTO original (row_id,reference) VALUES (9,"Reference 9");
select filler.row_id,original.reference from filler
left join original original on filler.row_id = original.row_id;
Which correctly gives:
row_id | reference
1 | Reference 1
2 | null
3 | Reference 3
4 | null
5 | null
6 | null
7 | null
8 | null
9 | Reference 9
10 | null
===============================
I now need to add several documents each containing up to 10 lines some
of which may be blank
So..
DROP TABLE IF EXISTS original;
CREATE TABLE original (doc_id, row_id int,reference varchar(15));
INSERT INTO original (doc_id,row_id,reference) VALUES (1,1,"Reference
1");
INSERT INTO original (doc_id,row_id,reference) VALUES (1,3,"Reference
3");
INSERT INTO original (doc_id,row_id,reference) VALUES (1,9,"Reference
9");
INSERT INTO original (doc_id,row_id,reference) VALUES (2,2,"Reference
2");
INSERT INTO original (doc_id,row_id,reference) VALUES (2,3,"Reference
3");
INSERT INTO original (doc_id,row_id,reference) VALUES (2,7,"Reference
7");
I now need to get a result like...
1 | 1 | Reference 1
1 | 2 | null
1 | 3 | Reference 3
1 | 4 | null
1 | 5 | null
1 | 6 | null
1 | 7 | null
1 | 8 | null
1 | 9 | Reference 9
1 | 10 | null
2 | 1 | Reference 1
2 | 2 | Reference 2
2 | 3 | null
2 | 4 | null
2 | 5 | null
2 | 6 | null
2 | 7 | Reference 7
2 | 8 | null
2 | 9 | null
2 | 10 | null
==========================================
Any thoughts
Roger
________________________________
From: roger.maynard
Sent: 12 March 2008 17:18
To: Phil
Cc: mysql@lists.mysql.com
Subject: RE: Auto Fill blank Rows
Yup! That's the kind of thing I was looking for - I just had a complete
blank moment
Thanks!!!
________________________________
From: Phil [mailto:[EMAIL PROTECTED]
Sent: 12 March 2008 17:05
To: roger.maynard
Cc: mysql@lists.mysql.com
Subject: Re: Auto Fill blank Rows
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
<[EMAIL PROTECTED]> 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
--
Help build our city at http://free-dc.myminicity.com !
