Re: Advanced(?) query question

"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

Regards,
jag Received on Fri Sep 19 2003 - 05:42:49 CEST