How to Do this Query

[shahid]

I am using vfp6 I am trying to do this query in two table
one name customer other one name Hist_order. Hist_order keep the information about history oder information. I am trying to get informantion about customer who place the order in may and also place the order in june.I try to run this query result is blank. Can any one tell me what I am doing wrong and How to do it right.


SELECT Customer.phone, Customer.name, Customer.strt_num,;
Customer.strt_name, Hist_ord.ord_date;
FROM customer INNER JOIN hist_ord ;
ON Customer.phone = Hist_ord.phone;
WHERE Hist_ord.ord_date >= "20020501";
AND Hist_ord.ord_date <= &quot;20020531&quot;;
AND Hist_ord.ord_date >= &quot;20020601&quot;;
AND Hist_ord.ord_date <= &quot;20020631&quot;;
GROUP BY Customer.phone

 

[danceman]

SELECT Customer.phone, Customer.name, Customer.strt_num,;
Customer.strt_name, Hist_ord.ord_date;
FROM hist_ord LEFT JOIN customer ;
ON Customer.phone = Hist_ord.phone;
WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
OR BETWEEN(Hist_ord.ord_date,&quot;20020601&quot;,&quot;20020631&quot;
GROUP BY Customer.phone

IF ORD_DATE is really a date field then you need to convert the you have in quotes to date type CTOD().
the main problem was the AND and lack of parenthese to group the date set. thats why I switched to between.

WHERE (Hist_ord.ord_date >= &quot;20020501&quot;;
AND Hist_ord.ord_date <= &quot;20020531&quot;
OR (Hist_ord.ord_date >= &quot;20020601&quot;;
AND Hist_ord.ord_date <= &quot;20020631&quot;
Attitude is Everything