How do I solve it

[KH75]

I want to make a query to retrieve active client from different areas


any client that is active in Cities (‘A’,’B’,’C’,’D’) as well as (‘X’ or ’Y’)?

any client that is active in Cities (’E’, ‘F’,’G’,’H’) as well as (‘X’ or ‘Y’)?


I write

Where
(( OrdDate Between FromDate And ToDate
And c.status = ‘V’
And City IN (‘A’,’B’,’C’,’D’,’X’))
OR
( OrdDate Between FromDate And ToDate
And c.status = ‘V’
And City IN (‘A’,’B’,’C’,’D’,’Y’))

AND


(( OrdDate Between FromDate And ToDate
And c.status = ‘V’
And City IN (‘E’,’F’,’G’,’H’,’X’))
OR
( OrdDate Between FromDate And ToDate
And c.status = ‘V’
And City IN (‘E’,’F’,’G’,’H’,’Y’))


Can any body tell me that I am doing righ or not. because I am not getting any results

Thanks

 

[bborissov]

Where (OrdDate Between FromDate And ToDate And c.status = ‘V’ And City IN (‘A’,’B’,’C’,’D’,‘E’,’F’,’G’,’H’,’X’,’Y’))
[/code


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[SQLBill]

You need to be clearer about what you are looking for. Does the client HAVE to be active in all cities A, B, C, D and also one of these X or Y?

Using a list with IN, is actually like an OR statement. So using WHERE Client IN ('A', 'B', 'C', 'D') will return a result if the client is IN only B (or only A, or only C, or only D).

It would be helpful if you showed sample data and what results you would like to see returned.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[KH75]

Thanks for help.

any client that has active order within a time frame(parameters) for group 1 Cities and group 2 Cities


any client that is active in Cities (‘A’,’B’,’C’,’D’) as well as (‘X’ or ’Y’)?

any client that is active in Cities (’E’, ‘F’,’G’,’H’) as well as (‘X’ or ‘Y’)?


Thanks

 

[SQLBill]

Then BBorissov's script will return that.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

To answer your original question...you aren't getting results because you have this:

WHERE
(this happens)
AND
(this happens).

The problem is the AND. Since your first part is for A,B,C,D,X,Y and your second part is for E,F,G,H,X,Y...the active client has to be in at least one city from A,B,C,D AND one city from E,F,G,H or in X or Y (which is in both parts of the WHERE)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

To answer your question...the issue with your script is the AND.

WHERE
(this happens)
AND
(this happens)

Both have to happen since there is an AND.

A client would have to exist in one of A,B,C,D AND in one of E,F,G,H; or in X or Y (which are in both parts of the WHERE) for it to succeed. For example...a client is in city A...that client also has to be (AND) in either city E,F,G,H. If not, nothing will be returned. But if a client is in either X or Y, you will get something back since it is in both parts of the WHERE.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[KH75]

Thanks for help