Re: Advanced(?) query question

"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

John,

Do you complete your kids' homework for them too? Received on Fri Sep 19 2003 - 06:39:27 CEST