[ic] HELP! query returns wrong values (but right number)

[Bill Eichin]

This is just too weird, so I thought I'd share with the rest of the 
class ;)  I'm dealing with the 'multiple categories per item' issue that 
I've seen here over the past few years by creating a separate table to 
search.   This works, almost....

I use this table and a scan query to build a link for the category list 
on the left-hand side of the page.  (Code included below.)  The tables 
are in postgresql, as follows (if you don't mind reading SQL):

CREATE TABLE department
(
  id serial NOT NULL,
  name varchar(128) NOT NULL,
  "desc" text,
  CONSTRAINT deptid PRIMARY KEY (id)
) 

CREATE TABLE aisle
(
  id serial NOT NULL,
  name varchar(64) NOT NULL,
  "desc" text,
  CONSTRAINT aisleid PRIMARY KEY (id)
) 


CREATE TABLE prodcat
(
  id serial NOT NULL,
  sku varchar(64) NOT NULL,
  dept int8 NOT NULL,
  aisle int8 NOT NULL,
  CONSTRAINT prodcatid PRIMARY KEY (id),
  CONSTRAINT catprod FOREIGN KEY (sku) REFERENCES products (sku) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT prodaisle FOREIGN KEY (aisle) REFERENCES aisle (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT proddept FOREIGN KEY (dept) REFERENCES department (id) ON UPDATE CASCADE ON DELETE CASCADE
) 


The first table, "department," replaces the "area" table in 
functionality.  I don't use the 'left' and 'top' designations; 
basically, I'm trying to normalize the structure somewhat.  (more on 
that later.)  The second table, "aisle," is the same, but replacing the 
"cat" table.

The third table, "prodcat", is the index showing what products (by sku) 
are in what combinations of department and aisle (or, prod_group and 
category).  The values are the numeric ID fields for the corresponding 
tables, so lookups should be fast.  In fact, here's the query I use as 
an example:

select * from products, prodcat 
  where prodcat.sku = products.sku 
  and prodcat.aisle = 3


It happens that "aisle 3" contains exatly three SKUs: CT24, CT25, and CT26.

What I'm trying to do here is emulate the previous functionality (click 
on a category on the left, and get a list of items in that category on 
the right).  It appears I have to convert this into a scan query to make 
that happen; this is what I built:

[perl tables="products prodcat department aisle"]
my $outstring;
my $dbh=$Sql{products} or return "Not shared.";
my $sql="SELECT id,name FROM department" or Log("Table unavail");
my $sth=$dbh->prepare($sql);
$sth->execute;
my @deptrow;
while(@deptrow = $sth->fetchrow()) {
        $outstring .= "<tr><td valign=top class='categorybar'><b>".$deptrow[1]."
</b></td></tr>\n<tr><td valign=top class='barlink'>";
        my $sqm="SELECT DISTINCT id,name FROM aisle WHERE aisle.id=prodcat.aisle
 AND prodcat.dept=".$deptrow[0];
        my $sti=$dbh->prepare($sqm) or Log("Table unavail");
        $sti->execute;
        my @airow;
        while(@airow = $sti->fetchrow()) {
                my $querystring = "&nbsp; &nbsp;<a href=".$Tag->area( { href =>
'scan', arg => 'st=db/sf=prodcat:aisle/se='.$airow[0].'/nu=1' } ).">".$airow[1].
"</a><br>";
                # Log($airow[1]."=".$airow[0]);
                $outstring .= $querystring;
        }
        $outstring .= "</td></tr>\n";
}
return $outstring."\n";
[/perl]


The results I see, however, are three different SKUs: K17, K17V, K17P.  
These are all in different categories, none of which are category 3.

So, I guess the questions are:
1) is there another way I can write the link so the query occurs in SQL?
2) is there another way to write the scan query so it works?
3) basically, what am I doing wrong here?

Thanks, in advance, for your support and input.

--Bill
  Eichin