Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32

    Unanswered: Fill Left Outer Join Nulls with previous value

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Diesel Dud View Post
    I was curious if it was possible to fill Left Outer Join Nulls with the previous value.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32
    Quote Originally Posted by r937 View Post
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that doesn't work

    Banana has no match, so how do you know what the "previous" match is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32
    Quote Originally Posted by r937 View Post
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32
    Quote Originally Posted by r937 View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •