QUERY PROBLEM Can not Run multiple AND Query

[shahid]

I am useing vfp6 I have to table one has all the information about customer other one has all the order history of customer ord_date is (c) field. date store as
ymd format example 20020801. I want to run a query where I
can find out about customer who place the order in may and
also place the order in june. Customer I want to see they must meet both condition may and june. when ever I run this query I get zero result. I can run as may or june but some how I don't know how to run order in may and june. some one please help me.


SELECT Customer.phone, Customer.name, Customer.strt_num,;
Customer.strt_name, Customer.city, Customer.state_prov,;
Customer.first_ord, Customer.last_ord;
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;20020630&quot;;
ORDER BY Customer.phone

 

[rgbean]

Well this isn't a FoxPro problem, but a 'logic' one. Since a Where conditon is applied to a single record, it can't vale a vale in both May and June. You'll either need to do a sub-query or two queries - I don't believe it can be done with a simple query. I'll try setting up some test data, and see if I can give you the exact syntax, although with this hint, maybe you can figure it out before I do.

Rick

 

[danceman]

SELECT Customer.phone, Customer.name, Customer.strt_num,;
Customer.strt_name, Customer.city, Customer.state_prov,;
Customer.first_ord, Customer.last_ord;
FROM customer INNER JOIN hist_ord ;
ON Customer.phone = Hist_ord.phone;
WHERE (Hist_ord.ord_date >= &quot;20020501&quot;;
AND Hist_ord.ord_date <= &quot;20020531&quot;
AND (Hist_ord.ord_date >= &quot;20020601&quot;;
AND Hist_ord.ord_date <= &quot;20020630&quot;
ORDER BY Customer.phone

then parenthesis () have to be used to group the first condicton may then group june. you could use the between, which would make it easier to visualy see the condiction.

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
AND BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;

Attitude is Everything

 

[Neil Toulouse]

...shouldn't this be an OR?

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
OR BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;
&quot;I love work. I could sit and stare at it for hours...&quot;

 

[Gandalf23]

I think so, I can't imagine a date that is >= &quot;20020501&quot;;
AND <= &quot;20020531&quot but also
>= &quot;20020601&quot; AND <= &quot;20020630&quot

Thus the OR is in it's place

 

[poeijer]

it shouldnt be an OR. since it is going about a customer placing 2 orders. it shouldnt be an AND since it only checks 1 record. the only solution is to use a subquery.

e.g.

select * from customer
where date is in june
and (select * from customer
where date is in may)

goodluck with it.

 

[danceman]

yes change to an OR in these places

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;20020630&quot;
ORDER BY Customer.phone

then parenthesis () have to be used to group the first condicton may then group june. you could use the between, which would make it easier to visualy see the condiction.

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
OR BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;

you do not need a sub query. Attitude is Everything

 

[poeijer]

quote/ I want to run a query where I can find out about customer who place the order in may AND
also place the order in june. /quote

this means he wants both. not just one of the two.
when u run your query u only get as a result the records of which the ord_date is in june OR in may OR in both. he is only interested in both if i read his question right..



'just my 2 cents'

 

[MCTCO]

this doesn't have anything to do with the query but if a customer moves and changes their phone number you will have a problem. Better off assigning a customer id to each customer.

 

[rgbean]

Try:

SELECT Customer.phone, Customer.name, Customer.strt_num,;
  Customer.strt_name, Customer.city, Customer.state_prov,;
  Customer.first_ord, Customer.last_ord;
 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.phone IN ;
    (SELECT Hist_ord.phone from Hist_ord ;
       WHERE Hist_ord.ord_date >= &quot;20020601&quot;;
         AND Hist_ord.ord_date <= &quot;20020630&quot;);
 ORDER BY Customer.phone

Rick

 

[danceman]

switching again poeijer is right it should be a AND

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
AND BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;
Attitude is Everything

 

[shahid]

RICK
Your code doesnot do the job.
show only june records.

danceman

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
OR BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;

above code does work but it show both customer one the
order in may but did not order june.
what I am looking for the customer place the order in may
also place the order in june. they must have order in may
and june. above code show the the customer they order in
may and did not order in june. If I use AND

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
AND BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;

query is blank. any help ?

 

[Schweiger]

As a sample I would take subst... Perhaps this leads to discussion, I would appreciate because its a performance issue I would be interessted in...
Try the following:

SELECT Customer.phone, Customer.name, Customer.strt_num,;
  Customer.strt_name, Customer.city, Customer.state_prov,;
  Customer.first_ord, Customer.last_ord;
 FROM  customer INNER JOIN hist_ord ;
   ON  Customer.phone = Hist_ord.phone;
 WHERE SUBST(Hist_ord.ord_date,1,6) = "200205" .OR.
       SUBST(Hist_ord.ord_date,1,6) = "200206"
 GROUP BY Customer.phone
 HAVING MIN(SUBST(Hist_ord.ord_date,1,6)) = "200205" .AND.
        MAX(SUBST(Hist_ord.ord_date,1,6)) = "200206"

Hope this helps
Andreas

 

[danceman]

WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
AND BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;

is the correct syntx to get from both data areas.

if you are getting nothing then you must not have any data that meets this date setting. Attitude is Everything

 

[rgbean]

Danceman,
WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
AND BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;

may be the correct syntax, but it's invalid logic! No individual record can satisfy both conditions.

Rick

 

[danceman]

rgbean, you made me think about that one, you are correct. try this

SELECT Customer.phone, Customer.name, Customer.strt_num,;
Customer.strt_name, Customer.city, Customer.state_prov,;
Customer.first_ord, Customer.last_ord;
FROM customer INNER JOIN hist_ord ;
ON Customer.phone = Hist_ord.phone;
WHERE BETWEEN(Hist_ord.ord_date,&quot;20020501&quot;,&quot;20020531&quot;
UNION;
SELECT Customer.phone, Customer.name, Customer.strt_num,;
Customer.strt_name, Customer.city, Customer.state_prov,;
Customer.first_ord, Customer.last_ord;
FROM customer INNER JOIN hist_ord ;
ON Customer.phone = Hist_ord.phone;
WHERE BETWEEN(Hist_ord.ord_date,20020601&quot;,&quot;20020630&quot;
ORDER BY Customer.phone ;
INTO CURSOR myresults

the final order by and the into cursor goes on the last select of the union.

sorry about the wrong postings earler.


Attitude is Everything

 

[rgbean]

Shahid,
Other than forgeting a GROUP BY clause, I believe it is correct, unless I'm making an assumption about the data that isn't true. For example, I assumed that phone was a unique field in the customer file. The following sample data and code seems to work as you requested. (Just cut and paste it into a .PRG file and run it.) Note: I eliminated most of the fields that had nothing to do with the linkage or selection criteria.

CREATE CURSOR customer (phone c(9), name c(20))
CREATE CURSOR hist_ord (phone c(9), ord_date c(8))

INSERT INTO customer VALUES ("111111111", "John Doe") && May - No June
INSERT INTO customer VALUES ("222222222", "Jane Bucks") && May & June
INSERT INTO customer VALUES ("333333333", "Mark Salot") && No May - June
INSERT INTO customer VALUES ("444444444", "Mary Hadalamb") && Multiple May - June
INSERT INTO customer VALUES ("555555555", "Larry Andmoe") && no May - No June


INSERT INTO hist_ord VALUES ("111111111", "20020102")
INSERT INTO hist_ord VALUES ("111111111", "20020501")
INSERT INTO hist_ord VALUES ("111111111", "20020701")
INSERT INTO hist_ord VALUES ("222222222", "20020102")
INSERT INTO hist_ord VALUES ("222222222", "20020502")
INSERT INTO hist_ord VALUES ("222222222", "20020602")
INSERT INTO hist_ord VALUES ("222222222", "20020702")
INSERT INTO hist_ord VALUES ("333333333", "20020603")
INSERT INTO hist_ord VALUES ("444444444", "20020529")
INSERT INTO hist_ord VALUES ("444444444", "20020530")
INSERT INTO hist_ord VALUES ("444444444", "20020603")
INSERT INTO hist_ord VALUES ("555555555", "20020102")
INSERT INTO hist_ord VALUES ("555555555", "20020701")

SELECT Customer.phone, Customer.name ;
 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.phone IN ;
    (SELECT Hist_ord.phone from Hist_ord ;
       WHERE Hist_ord.ord_date >= &quot;20020601&quot;;
         AND Hist_ord.ord_date <= &quot;20020630&quot;);
 GROUP BY Customer.phone ;
 ORDER BY Customer.phone

Rick

 

[poeijer]

Like I said.
just use a subquery to retrieve all data from 1 month.
and search that data ( so u know for sure it occurs in May) to also get out data for the 2nd month.