[ic] Multiple Table Select under MySql

[Kevin Walsh]

> 
> I am using IC 4.8.3 and I am trying to create a componenet similar to the
> promo component but I want to include a restriction that will only give me
> the products that are on promotion and are members of a certain category.
> 
> Here is the part of the SQL statment I have created for the promo component:
> 
> [query arrayref=main
> sql="
> SELECT sku, timed_promotion, start_date, finish_date
> FROM merchandising, products
> WHERE featured = '[control promo_type specials]'
> AND merchandising.sku = products.sku
> AND products.category='COOK'
> "][/query]
> [perl tables="__UI_MERCH_TABLE__ merchandising"]
> my @out;
> my $ref;
> my $db;
> delete $Scratch->{promo_codes};
> my $date = $Tag->time( { body => '%Y%m%d' } );
> $ref = $Tmp->{main} or return;
> for(@$ref) {
> my $line = $_;
> push(@out, $line->[0]), next if ! $line->[1];
> next if $line->[2] gt $date;
> next if $line->[3] lt $date;
> push @out, $line->[0];
> }
> $Scratch->{promo_codes} = join(' ', @out);
> return;
> [/perl]
> 
The error that stands out is your ambiguous use of "sku" in the SELECT.
Change to the following:

    SELECT products.sku, timed_promotion, start_date, finish_date

You could also change this line:

    $ref = $Tmp->{main} or return;

To this:

    $ref = $Tmp->{main} or die 'Oh no, not again!';

If your SQL fails, your message in the error.log files will help
you track down the cause.  Of course, if you were in the habit of
checking your error.log files, you'd probably have noticed the error
message indicating the ambiguous use of the "sku" column. :-)

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/