[ic] SQL query on multiple databases.

[Mark Johnson]

If you're talking about a join, then there is no such thing as "which
table the result came from". However, I assume you are not talking about
a join, but rather collecting the results of multiple searches into one
bucket. Here's some simple code for two tables; you can extend it to N
from there. I'll assume a character sort on the 'foo' column.

[query st=db hashref=table1 sql="select 'table1' as tablename, code, foo
                                 from table1"][/query]
[query st=db hashref=table2 sql="select 'table2' as tablename, code, foo
                                 from table2"][/query]

[perl]
  my @all = (@{ $Tmp->{table1} },@{ $Tmp->{table2} }); # Combining
results.
  my ($row, $out);

  foreach $row ( sort { $a->{foo} cmp $b->{foo} } @all ) { # Sorting
     $out .= <<EOP;
$row->{code} is the key for this row from $row->{tablename}<br>
The sort is ascending character based on $row->{foo}<p>
EOP
   }
   return $out;
[/perl]

For more detail about queries, consult an SQL book or some websites
describing syntax. For more detail about the sort function in perl, the
man pages should give you a good description of the comparison operators
and what $a and $b do.

Mathew Jones wrote:
> 
> I would like to search several seperate databases at the same time, returning
> the results on the same page.
> What I previously set up was 4 seperate [QUERY SQL] tags, which each returned
> independent sets of results to the page. Ideally I would like to be able to
> pull all the results into the same array or hash so i can sort them by the
> price field and they appear as one combined set of results.
> I have looked on the developers site and through the old mailing list
> questions and only got more confused. I am not a perl programmer by nature,
> and need a little help on finding the best possible solution to my problem.
> So i guess I have a couple of questions:
> 1) How do I get all the seperate search  results into the same
> array/hash/whatever I need to use?
> 2) How do I sort the data based on 1 particular field of the combined results
> 
> 3)How can I push an 'identifier' into each row returned so i can identify
> which db the result came from?
> 
> Thanks
> 
> Mat
> 
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

-- 
Mark Johnson
Senior Developer - Professional Services
Red Hat, Inc.
E-Business Solutions
markj@redhat.com
703-456-2912