Re: Advanced(?) query question

"John Gilson" <jag_at_acm.org> wrote in message news:Q%Bab.7250$nU6.1369268_at_twister.nyc.rr.com... > "Bob Badour" <bbadour_at_golden.net> wrote in message news:W_wab.235$9e2.21597243_at_mantis.golden.net...
> > "John Gilson" <jag_at_acm.org> wrote in message
> > news:ZWuab.6834$nU6.1269431_at_twister.nyc.rr.com...
> > > "Anders Steinlein" <dinhelt_at_online.noSPAM> wrote in message
> > > news:MPG.19d4790bf8c57019896dd_at_news.online.no...
> > > > I'm not sure I've come to the right place, so any help would be most
> > > > appreciated.
> > > >
> > > > In two tables, cities and countries, the first references the latter
> > > > (foreign key). I want to retrieve the country ids (the column shared
by
> > > > both tables) where the values in the password column in the
countries
> > > > table is null, but those country ids found should not have more than
10
> > > > occurences in the cities table.
> > > >
> > > > Can this be done?
> > > >
> > > > Thanks in advance,
> > > > \Anders
> > >
> > > CREATE TABLE Countries
> > > (
> > > country_id CHAR(2) NOT NULL PRIMARY KEY,
> > > password VARCHAR(10) NULL
> > > )
> > >
> > > CREATE TABLE Cities
> > > (
> > > country_id CHAR(2) NOT NULL REFERENCES Countries (country_id),
> > > city VARCHAR(20) NOT NULL,
> > > PRIMARY KEY (country_id, city)
> > > )
> > >
> > > SELECT CNT.country_id
> > > FROM Countries AS CNT
> > > INNER JOIN
> > > Cities AS CIT
> > > ON CNT.country_id = CIT.country_id
> > > WHERE CNT.password IS NULL
> > > GROUP BY CNT.country_id
> > > HAVING COUNT(*) <= 10

> >

> >

>
> Bob, of course not, but there's nothing immediately discernable to me
> that would lead me to conclude, or even strongly suspect, that this is
> a homework assignment.  If this is your conclusion, please substantiate.

There is nothing immediately discernable to me that would lead me to conclude, or even strongly suspect, that this is not a homework assignment. If this is your conclusion, please substantiate. Received on Fri Sep 19 2003 - 14:03:03 CEST