>I'm trying to do a complex query and I can't seem to get the syntax right. I have 3 db's that are part of an invoice system (customer, summary and detail).
>
>I want to select customers in GA (customer.bill_state) who's sales over the last 12 months is over $1,000 (sum(summary.total)) who have never bought an inventory item that starts with 'SE' (substr(detail.inv_id,1,2))
>
>The following is generated from the Foxpro Query. But it does not work. The problem is I need to first pull out those who have never bought an 'SE' product, then somehow total sales in the last year pull ones that are over $1,000.
>
>SELECT Customer.name, Customer.bil_add1, Customer.bil_add2,;
> Customer.bil_add3, Customer.bil_city, Customer.bil_st, Customer.bil_zip,;
> Customer.title, Customer.first, Customer.last;
> FROM customer INNER JOIN summary;
> INNER JOIN detail;
> INNER JOIN ;
> INNER JOIN summary;
> INNER JOIN summary ;
> ON sum(summary.total) > 1000.00 ;
> ON Summary.shp_date >= (date()-365) ;
> ON substr(detail.inv_id,1,2) <> 'SE' ;
> ON Summary.inv_num = Detail.inv_num ;
> ON Customer.cust_id = Summary.cust_id;
> WHERE Customer.bil_st = "GA"

Is customer, summary, and detail related parent-child-grandchild? I'm assuming that it is. It should be something like this:

SELECT customer.name, customer.allthatotherstuff
FROM customer INNER JOIN summary ON customer.cust_id = Summary.cust_id
WHERE customer.bil_st = "GA" AND customer.cust_id NOT IN
(SELECT customer.cust_id FROM customer
INNER JOIN summary ON customer.cust_id = summary.cust_id
INNER JOIN detail ON summary.inv_num = detail.inv_num
WHERE substr(detail.inv_id,1,2) <> 'SE')
HAVING SUM(summary.total) > 1000.00

If that doesn't work, try putting a GROUP BY 1,2 before the HAVING clause. I haven't tested this, but I think that it would work substantially as written unless I have completely understood your table structure. It won't run fast, because a query with a NOT IN clause is not rushmore optimizable, but that is probably unavoidable. There was a thread a while back about clever ways to get around the problem, none of which were satisfactory at the time.

A few observations: 1) I hope you don't really have a "name" field. That is a reserved word, and could cause all kinds of problems impossible to anticipate. 2) Many people here will tell you that nested joins are confusing or unreliable. I don't use them. 3) The vfp view query builder is considered unreliable by many if you have three or more tables, and it makes nested joins. People recommend downloading eView from the UT. I think that it won't create a query from scratch. I haven't used it much.