Query or filtering a table

hi rainbowsoftware,

for filter:

table1.filter := 'City='+quotedstr('City2');
table1.filtered := True;

for query:

query1.sql.clear;
query1.sql.add('select * from TheTable where city = '+quotedstr('City2');
query1.Open;

meikl

Sorry, I asked for the wrong solution!
What I want is to know:

Which Countries contains City1 AND City2 ?

Or

Which Countries contains City1 AND City2 AND City3 ?

And I want the countries to be listed Distinctly.

Joern

You can adjust the SQL statement to the following:

1) use an or, if you have a finite or known quantity of selection options (i.e., you know you have only three cities you want as the search criteria)

select distinct Country from TableName where city = x or city = y or city = z

2) if you don't know how many cities (if you wanted to show all countries containing cities whose population exceeds 25000, for example), you can use a sub-select

select distinct country from tablename where city in (select city from tablename where population > 25000)

the distinct clause will give you more than one record if you include the city field, obviously, as it will have to show you a record for each country/city combination -- so you will need to drop the city field if you want only one record to show per country.

Thanks for the answers, but I do not want to use OR, I want to use AND.
And that is the problem, because when using AND no records are shown.

of course not...think about what you're saying:

show me the different country values where the city field = x and the city field = y and the cityfield = z

one field can't have three values...by analogy

show me the all the people whose hair is brown and red and black

that's just not how SQL works...you have to say

show me all the people whose hair is brown or red or black

to get a list of people

you know, the best book on SQL that I've seen is called "SQL for Dummies" -- it's got a lot of advanced topics, but it's written well.  

Thanks for the hint.
Joern