I was curious if it was possible to fill Left Outer Join Nulls with the previous value.


e.g.


The left outer join returns:

ColA ID A B
Apple 1 Apple 2
Apple 2 Apple 10
Banana NULL NULL NULL
Orange 3 Orange 3
Pear 4 Pear 16


while I'd like to have

ColA ID A B
Apple 1 Apple 2
Apple 2 Apple 10
Banana 2 Apple 10
Orange 3 Orange 3
Pear 4 Pear 16


CREATE TABLE IF NOT EXISTS `Table1` (
`A` varchar(25) NOT NULL,
PRIMARY KEY (`A`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `Table1` (`A`) VALUES
('Apple'),
('Banana'),
('Orange'),
('Pear');

CREATE TABLE IF NOT EXISTS `Table2` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`A` varchar(25) NOT NULL,
`B` int(11) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `Table2` (`ID`, `A`, `B`) VALUES
(1, 'Apple', 2),
(2, 'Apple', 10),
(3, 'Orange', 3),
(4, 'Pear', 16);

SELECT *
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.A = Table2.A;


Thanks!

yes, if you would be so kind as to provide the means by which the previous row can be detected

remember, the rows in a table have no sequence

previous row would be identified using Table2.ID
e.g. ID=1 is ID=2's previous row
Thanks

no, that doesn't work

Banana has no match, so how do you know what the "previous" match is?

I see what you mean.

Hypothetically, if I were to insert the LEFT OUTER JOIN result (in the order I require) in a workfile that has an auto-increment ID as primary key, I could update NULL values with previous values, right?

i don't think i can offer any further advice except to say you should consider doing this in your application code and not in the database, temp tables or otherwise

the reason i can't give more solid advice is because we really aren't talking about apples and bananas, and between the two of us, only one knows what it's really all about

Thanks a lot, I appreciate your help.