Hi

I have a table 'major' containing only two columns 'city' and 'pop'.

I have another table 'country' containing 100 columns.

I want to SELECT from COUNTRY those rows where the city's pop is more than
10000.

SELECT ind,fish,city FROM country WHERE country.city=major.city AND
major.city.pop<10000

I appreciate I cannot say "major.city.pop" but how do I access 'pop' of
'city' in 'major'?

Regards
John

Am 13.02.2011 09:58, schrieb John:

Which seems to be a broken design to me... Tables with that many columns
almost always indicate some mistakes in the design.

Which confirms my suspicion about the broken design: A city is not an
attribute of a country, it's the other way round. Except, the city means
the capital here...

Why 'pop' of 'city'? You said, your table is 'major', containing only
two columns 'city' and 'pop'. So you access the column 'pop' of 'major':
major.pop

HTH, Helmut

"Helmut Chang" <usenet (AT) helmutchang (DOT) at> wrote

Hi

Thank you for your reply.

My question is how to correct the WHERE statement so that I only extract
those rows where the country.city and the major.city are the same but the
major.pop is less than 10000.

Thank you.

Regards
John
#



>

John wrote:
SELECT c.ind, c.fish, c.city, m.pop
FROM country c
INNER JOIN major m ON(c.city = m.city)
WHERE m.pop > 10000

SELECT c.ind, c.fish, c.city, m.pop
FROM country c
INNER JOIN major m ON(c.city = m.city)
WHERE m.pop < 10000


But really, see if you can do something about the tables you are using, 100
columns? Naming of the tables should be more propitiate, the table "major"
should maybe been called "city_population" and instead of having the name as
the index, just had an auto_increment index, for example if a city changes
name (Leningrad -> Petersburg), then you don't want to go and change the name
in many tables, you want just to do that in one place.


--

//Aho

On Feb 13, 8:58*am, "John" <john1... (AT) yahoo (DOT) com> wrote:
As others have pointed out, yur schema is fundamentally flawed. For
instance What is to stop 2 countries having cities with the same name?

Captain Paralytic wrote on 13.02.2011 13:33:
That is not something that should be prevented.

It's not really uncommon, just think about the many U.S. cities that were named after european cities (Berlin, Paris, London, Madrid)

Regards
Thomas

Hans Castorp wrote:
I am not sure how many Birminghams or Newcastles there are around the world.


Alabama has one, the UK another.

Newcastle is in South Africa as well as the UK. SA also has an Utrecht,
Wartburg, Worcester, Richmond, Margate, Kempton Park, Bewley, Bedford..

Boston exists in the USA and the UK.


The whole of the post colonial English speaking world is littered with
towns of the same name.

On Feb 13, 12:51*pm, Hans Castorp <SOZRBLNTL... (AT) spammotel (DOT) com> wrote:
That is my point. The schema as described cannot handle this situation.

Captain Paralytic:

Try and get the village of Baarle into a DB-schema! :-)
http://en.wikipedia.org/wiki/Baarle-Hertog

Oh, and what about Nicosia?


--
Erick

Erick T. Barkhuis:

....and in which country is this library situated?
http://en.wikipedia.org/wiki/File:Ha...BorderLine.jpg



--
Erick