- $query .= " AND id NOT IN (SELECT p.id
- FROM project p, acc_trans a
- WHERE p.id = a.project_id)
- AND id NOT IN (SELECT p.id
- FROM project p, invoice i
- WHERE p.id = i.project_id)
- AND id NOT IN (SELECT p.id
- FROM project p, orderitems o
- WHERE p.id = o.project_id)";
+ my %col_prefix = ("ar" => "global", "ap" => "global", "oe" => "global");
+ my $first = 1;
+
+ $where .= qq|AND id NOT IN (|;
+ foreach my $table (qw(acc_trans invoice orderitems rmaitems ar ap oe)) {
+ $where .= "UNION " unless ($first);
+ $first = 0;
+ $where .=
+ qq|SELECT DISTINCT $col_prefix{$table}project_id FROM $table | .
+ qq|WHERE NOT $col_prefix{$table}project_id ISNULL |;
+ }
+ $where .= qq|) |;