Query or filtering a table

I have got a datatable with 2 fields (Country and City). In the table I have got more countries with the same city.

Country      City

Country1      City1
Country1      City2
Country1      City3
Country2      City2
Country3      City1
Country3      City2
Country4      City1

How can I query or filter the table, if I want to know which Countries contains City2 ?

So that the result will be like this:
Country1      City2
Country2      City2
Country3      City2

Hope it's understandable.
Joern
rainbowsoftwareAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
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
0
NorthXCommented:
Couple of slight modifications to Meikl's response:

1) To obtain a single reference for each country:

query1.sql.text := 'select distinct Country, City from TableName where city = ' + quotedstr('City2');
query1.open;

2) To order the country names, you could add an order by clause:

query1.sql.text := 'select distinct Country, City from TableName where city = ' + quotedstr('City2') + ' order by Country';

I rarely use filters, and would suggest you stick with queries unless you have a true reason to use a TTable.

Hope that helps,

Gary

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rainbowsoftwareAuthor Commented:
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


0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

NorthXCommented:
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.
0
rainbowsoftwareAuthor Commented:
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.
0
NorthXCommented:
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.  
0
rainbowsoftwareAuthor Commented:
Thanks for the hint.
Joern
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.