[ic] [query] on multiple tables/databases

Michael Lehmkuhl interchange-users@icdevgroup.org
Thu Sep 26 10:40:04 2002


Hello.

I'm attempting to get a list of skus and quantities from the orderline
database for all transactions that aren't deleted or TEST transactions.
We're running with MySQL.

I don't get any errors in either the catalog error.log or the IC error.log
when running this, but the $outfile file is not created.  If I take the same
query and run it from another MySQL client, the file is created with the
correct data inside.

[perl tables='orderline transactions']
my $ep_next_order_num = $CGI->{ep_next_order_num};
$ep_next_order_num = 0 unless ($ep_next_order_num);

$sql = "SELECT orderline.sku,orderline.quantity
    INTO OUTFILE '$outfile' FROM orderline,transactions
    WHERE (orderline.order_number >= '$ep_next_order_num')
    AND (orderline.order_number = transactions.order_number)
    AND (transactions.deleted = 0)
    AND (orderline.order_number NOT LIKE 'TEST%')";
    
$result_hasharray = $Tag->query({
    table    => 'orderline,transactions',
    sql     => $sql,
    hashref => 'my_results',  },
);
[/perl]


I suppose I could first do a query to get the list of order_numbers from the
transactions database, and then do something like this:

SELECT sku,quantity FROM orderline WHERE order_number NOT IN ('<order
numbers from transactions database go here>')

I'd rather do it all in one query if possible, though.  Any help on this is
appreciated.

Thanks.

-- 
Michael Lehmkuhl <michael@electricpulp.com>
Partner, Electric Pulp

1.605.988.0177 : 1.888.722.PULP
electricpulp.com