Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Merging SQL Results

by johnirl (Monk)
on Aug 05, 2002 at 08:09 UTC ( #187603=perlquestion: print w/replies, xml ) Need Help??

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;

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;

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)

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).

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" }
    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!
      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;

      Ireland; Germany; john ; cork ; holger ; stuttgart ; 21 ; pat ; dublin ; jens ; berlin ; 22 ; Eoin ; galway ; -- ; -- ; 33 ; -- ; -- ; stefan ; dresden ; 23 ;

      j o h n i r l .

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

        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)
        I haven't been able to test this, so please let me know if it works!
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.

    ¤

      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)

        Visit Acronym Finder to find out.
        (Though I managed to get it by context ;-)
        regards,
        tomte
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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://187603]
Approved by vagnerr
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (4)
As of 2022-10-05 22:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (25 votes). Check out past polls.

    Notices?