my ($ascdesc) = ($bs eq 'buy')
? ('DESC')
: ('ASC');
-
+#INNER JOIN
my $islands= $dbh->prepare(
- "SELECT islandid, archipelago, islandname, sum(qty) as tqty
- FROM $bs NATURAL JOIN islands
- WHERE commodid = ?
- GROUP BY islandid,
+ "SELECT islands.islandid AS islandid, archipelago, islandname,
+ sum(qty) as tqty
+ FROM islands LEFT OUTER JOIN $bs offers
+ ON islands.islandid == offers.islandid AND commodid == ?
+ GROUP BY islands.islandid,
ORDER BY archipelago, islandname"
);
% while ($island= $islands->fetchrow_hashref) {
% my $islandid= $island->{'islandid'};
% $offers->execute($commodid, $islandid);
-% my ($offer, $bestprice, $median, $marginal, @beststalls);
+% my ($offer, $bestprice, $marginal, @beststalls);
% my $tqty= $island->{'tqty'};
-% my $cqty= 0;
-% my $bestqty= 0;
-% my $approxqty= 0;
+% my $cqty= '';
+% my $bestqty= '';
+% my $approxqty= '';
+% my $median= '-';
% while ($offer= $offers->fetchrow_hashref) {
% my $price= $offer->{'price'};
% my $qty= $offer->{'qty'};
-% defined $bestprice or $bestprice= $price;
+% length $bestqty or $bestprice= $price;
% if ($price == $bestprice) {
% $bestqty += $qty;
% push @beststalls, $offer->{'stallname'};
% }
% $cqty += $qty;
-% if ($cqty*2 >= $tqty && !defined $median) {
+% if ($cqty*2 >= $tqty && $median eq '-') {
% $median= $price;
% }
% if ($bestprice*9 <= $price*10 and
% $cqty == $tqty or die "$bs $cqty $tqty $commodid $islandid ";
<tr> <td><% $island->{'archipelago'} |h %>
<td><% $island->{'islandname'} |h %>
- <td><% $nstalls==1 ? $beststalls[0] : "$nstalls offers" |h %>
- <td><% $bestprice %>
+ <td><% $nstalls==0 ? '-' :
+ $nstalls==1 ? $beststalls[0] : "$nstalls offers" |h %>
+ <td><% length $bestqty ? $bestprice : '-' %>
<td><% $median %>
<td><% $bestqty %>
<td><% $approxqty %>
- <td><% $tqty %>
+ <td><% $cqty %>
</tr>
% }
</table>