Re: Advanced(?) query question

"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.

Regards,
jag Received on Fri Sep 19 2003 - 13:45:52 CEST