?
Solved

Query or filtering a table

Posted on 2000-01-05
7
Medium Priority
?
175 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:rainbowsoftware
  • 3
  • 3
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 2327266
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
 

Accepted Solution

by:
NorthX earned 300 total points
ID: 2327494
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
 

Author Comment

by:rainbowsoftware
ID: 2328314
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Expert Comment

by:NorthX
ID: 2328893
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
 

Author Comment

by:rainbowsoftware
ID: 2331310
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
 

Expert Comment

by:NorthX
ID: 2332204
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
 

Author Comment

by:rainbowsoftware
ID: 2336099
Thanks for the hint.
Joern
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question