Merging SQL Results

johnirl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, in my current program I have a routine that retrieves results from my database. Currently I display the results from each statment in the following form.

Result 1;
R1something1 ; R1something2 ;
R1something5 ; R1something6 ; 
R1something7 ; R1something8 ;

Result2;
R2something1 ; R2something2 ;
R2something3 ; R2something4 ; 
R2something5 ; R2something6 ; 
R2something7 ; R2something8 ;
R2something9 ; R2something 10;
[download]

But I want to display them beside each other to aid with comparisons. Like so:

Result 1                      Result2
R1something1 ; R1something2 ; R2something1 ; R2something2 ;
     --      ;      --      ; R2something3 ; R2something4 ;
R1something5 ; R1something6 ; R2something5 ; R2something6 ;
R1something7 ; R1something8 ; R2something7 ; R2something8 ;
     --      ;      --      ; R2something9 ; R2something10;
[download]

I also need to know how I would substitute a null value with a "--" in my output file as I want the results that satisfy the same conditions beside each other and the results with no corresponding results in the other table should still be displayed i.e. R2Something 9 & 10 above.
I am retrieving results row by row. But I am unsure of how to switch between the statements. Will I have to retrieve all results for one sql statement and then begin the second? Or can I switch between the two? Or is there some funky perl module that can take care of this for me?
Any help you can give is appreciated
Thanks Monks

JohnIrl

Sum day soon I'Il lern how 2 spelI (r tYpe)

Comment on Merging SQL Results Select or Download Code

Replies are listed 'Best First'.
Re: Merging SQL Results by dws (Chancellor) on Aug 05, 2002 at 08:20 UTC
Since you aren't showing the queries, I can only speculate that what you're looking for is an "outer join" to do the merge. (Consult your RDBMS for details on doing an outer join. Details vary.) You'll end up with NULLs where there are no corresponding rows. You'll need to manually substitute "--" for NULL (undef).	[reply]
Re: Merging SQL Results by physgreg (Scribe) on Aug 05, 2002 at 08:26 UTC
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!	[reply] [d/l]
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]
Re: Merging SQL Results by frankus (Priest) on Aug 05, 2002 at 09:59 UTC
WRT to the Database: which database are you using Oracle and Sybase use different functions that can achieve what you want. Using the built in functions will decrease the network trafic as extra rows are collated.IMO\|E -- Brother Frankus. ¤	[reply]
Re: Re: Merging SQL Results by johnirl (Monk) on Aug 05, 2002 at 10:05 UTC
Thanks Frankus I'm using DB2 but I have no idea what you mean when you say "WRT" sorry!! j o h n i r l . Sum day soon I'Il lern how 2 spelI (nad tYpe)	[reply]
Re: Re: Re: Merging SQL Results by Tomte (Priest) on Aug 05, 2002 at 11:26 UTC
Visit Acronym Finder to find out. (Though I managed to get it by context ;-) regards, tomte	[reply]
Re: Merging SQL Results by markjugg (Curate) on Aug 05, 2002 at 21:30 UTC
If you are using Postgres, and you want to apply the logic, "when a field is null, return '--'", then using the COALESCE or CASE functions are a good solution for that. You can read more them here. -mark	[reply]


P is for Practical
	PerlMonks