SELECT when WHERE condition is in another table

#11

On 13-02-11 17:10, Erick T. Barkhuis wrote:

Quote:

Captain Paralytic:

On Feb 13, 12:51 pm, Hans Castorp <SOZRBLNTL... (AT) spammotel (DOT) com> wrote:
Captain Paralytic wrote on 13.02.2011 13:33:

What is to stop 2 countries having cities with the same name?

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

That is my point. The schema as described cannot handle this
situation.

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

Oh, and what about Nicosia?

Baarle-Hertog is in only in 1 'country'....

and luckily borders are not in the database as far as the OP
mentioned...

--
Luuk

#12

On 2/13/2011 11:10 AM, Erick T. Barkhuis wrote:

Quote:

Captain Paralytic:

On Feb 13, 12:51 pm, Hans Castorp<SOZRBLNTL... (AT) spammotel (DOT) com> wrote:
Captain Paralytic wrote on 13.02.2011 13:33:

What is to stop 2 countries having cities with the same name?

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

That is my point. The schema as described cannot handle this
situation.

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

Oh, and what about Nicosia?

Erick,

I just followed you link - I love it! I knew borders and things could
get complicated over there, but this has to be one of best.

I know it's probably not funny to the residents, but to an outsider it's
great. Thanks for the laugh.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#13

Jerry Stuckle:

Quote:

On 2/13/2011 11:10 AM, Erick T. Barkhuis wrote:

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

Quote:

I just followed you link - I love it! I knew borders and things
could get complicated over there, but this has to be one of best.

This is what I considered the best part of the article (apart from the
little map):
"The border is so complicated that there are some houses that are
divided between the two countries. There was a time when according to
Dutch laws restaurants had to close earlier. For some restaurants on
the border it meant that the clients simply had to change their tables
to the Belgian side."

#14

On 2/13/2011 3:22 PM, Erick T. Barkhuis wrote:

Quote:

Jerry Stuckle:

On 2/13/2011 11:10 AM, Erick T. Barkhuis wrote:

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

I just followed you link - I love it! I knew borders and things
could get complicated over there, but this has to be one of best.

This is what I considered the best part of the article (apart from the
little map):
"The border is so complicated that there are some houses that are
divided between the two countries. There was a time when according to
Dutch laws restaurants had to close earlier. For some restaurants on
the border it meant that the clients simply had to change their tables
to the Belgian side."

Yea, I read that part to my wife and she almost died laughing. It is
good

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#15

On Feb 13, 7:33*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On Feb 13, 8:58*am, "John" <john1... (AT) yahoo (DOT) com> wrote:

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

As others have pointed out, yur schema is fundamentally flawed. For
instance What is to stop 2 countries having cities with the same name?

Cap,

this design is what you get when you let kids "design" databases. My
first thought was that this looks like someones homework, maybe not,
but when asking how to join two tables, it certainly looks like it.

#16

On Sun, 13 Feb 2011 13:21:44 +0000, The Natural Philosopher
<tnp (AT) invalid (DOT) invalid> wrote:

Quote:

Newcastle is in South Africa as well as the UK.

More than one in the UK

--
Geoff
Newcastle under Lyme

#17

On Feb 16, 1:02*am, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:

Quote:

On Feb 13, 7:33*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On Feb 13, 8:58*am, "John" <john1... (AT) yahoo (DOT) com> wrote:

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 morethan
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

As others have pointed out, yur schema is fundamentally flawed. For
instance What is to stop 2 countries having cities with the same name?

Cap,

this design is what you get when you let kids "design" databases. * My
first thought was that this looks like someones homework, maybe not,
but when asking how to join two tables, it certainly looks like it.

The way I look at it is that, if designing relational database
applications is so easy that anyone can do it, why did I spend so long
studying at university. Then I look at the stuff designed by others
and I know why!

#18

Captain Paralytic wrote:

Quote:

On Feb 16, 1:02 am, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:
On Feb 13, 7:33 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

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

this design is what you get when you let kids "design" databases. My
first thought was that this looks like someones homework, maybe not,
but when asking how to join two tables, it certainly looks like it.

The way I look at it is that, if designing relational database
applications is so easy that anyone can do it, why did I spend so long
studying at university. Then I look at the stuff designed by others
and I know why!

Well I didn't study it at university, but its just another classic
design problem of the sort I did study at university.

Design is all about establishing the relations between components to the
resulting construction, be it mechanical, electrical or software,
performs in the required way.

Splitting things into two tables with no relationship, is like trying to
build a gearbox where the gears don't mesh.

The wheels may turn, but their motion can be completely independent :-)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SELECT when WHERE condition is in another table

comp.databases.mysql comp.databases.mysql

Re: SELECT when WHERE condition is in another table - 02-13-2011 , 11:02 AM

Re: SELECT when WHERE condition is in another table - 02-13-2011 , 01:39 PM

Re: SELECT when WHERE condition is in another table - 02-13-2011 , 02:22 PM

Re: SELECT when WHERE condition is in another table - 02-13-2011 , 03:01 PM

Re: SELECT when WHERE condition is in another table - 02-15-2011 , 07:02 PM

Re: SELECT when WHERE condition is in another table - 02-16-2011 , 03:37 AM

Re: SELECT when WHERE condition is in another table - 02-16-2011 , 04:47 AM

Re: SELECT when WHERE condition is in another table - 02-16-2011 , 07:17 AM