Re: Merging SQL Results

How do you know whether a line matches or not? Ie what do the (for example) Rlsomething5 fields have in common for the program to know that they are related? Assuming that there is a way, you could keep a buffer in an array or hash of the first result set (if it is not too big), and then as you get the second result set, prepend the corresponding element of the first set, or a '--' if it not set. Without knowing much about the data it's hard to give a code example, but here is a guess (without the SQL stuff, as it's too early on Monday morning :-)):

foreach $result (@result_1) {
    $resultHash {${$result}[0]} = ${$result}[1];
}

foreach $result (@result_2) {
    if (defined ($resultHash {${$result}[0])) {
        print "${$result}[0] , $resultHash[1]";
    } else {
        print " -- --";
    }
    print "${$result}[0] , ${$result}[1]\n"
}
[download]

I've assumed that the first column is some kind of key, and treated the results as an array of arrays. Hope this makes some sense!

Comment on Re: Merging SQL Results Download Code

Replies are listed 'Best First'.
Re: Re: Merging SQL Results by johnirl (Monk) on Aug 05, 2002 at 08:40 UTC
Ok I'm obviously not after making myself clear. Probably for the same reason you have no SQL samples in your answer ;-) Basically what I want to do is display two tables beside each other. Thats it. So say I have two statements `select name, address fom table1 where age > 20 and country = "Ireland"` `select name, address fom table1 where age > 20 and country = "Germany"` instead of displaying two tables one above the other (as below) I'd like to merge them (as further below) `Ireland; john;cork;21; pat;dublin;22; Eoin;galway;33; Germany; holger;stuttgart;21; jens;berlin;22; stefan;dresden;23;` [download] `Ireland; Germany; john ; cork ; holger ; stuttgart ; 21 ; pat ; dublin ; jens ; berlin ; 22 ; Eoin ; galway ; -- ; -- ; 33 ; -- ; -- ; stefan ; dresden ; 23 ;` [download] j o h n i r l . Sum day soon I'Il lern how 2 spelI (nad tYpe)	[reply] [d/l] [select]
Re: Re: Re: Merging SQL Results by physgreg (Scribe) on Aug 05, 2002 at 09:49 UTC
From my rather hazy recollection of SQL: `SELECT ir.name, ir.address, ge.name, ge.address, ir.age, ge.age FROM table1 AS ir OUTER JOIN table1 AS ge ON ir.age = ge.age WHERE ir.age > 20 AND (ir.country = 'Ireland' AND ge.country = 'Germany') OR (ge.country = 'Germany' AND ir.country IS NULL) OR (ir.country = 'Ireland' AND ge.country IS NULL)` [download] I haven't been able to test this, so please let me know if it works!	[reply] [d/l]