Re: Advanced(?) query question

Anders Steinlein <dinhelt_at_online.noSPAM> wrote in message news:<MPG.19d485d86d10bc3d9896df_at_news.online.no>...
> In article <ZDuab.216$uO1.20255791_at_mantis.golden.net>, bbadour_at_golden.net
> says...
> > >
> > > 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?
> >
> > Yes.
>
> Okaaay. Care to help me out?
>
> \Anders

comp.databases would probably have been a better place to ask, but

create table countries (

    countrid char(??) not null primary key,     countryname varchar(??)
)

create table cities (

    cityid char(??) not null primary key,     cityname varchar(??)
    countryid char(??) not null,
    foreign key (countryid) references countries )

select countrid from (

    select c.countrid, (select count(1) from cities where countryid = c.countryid) as cnt from countries c
) X where cnt < 10

or you could use an outer join (to capture countries without cities) and a group by / having clause

HTH
/Lennart Received on Fri Sep 19 2003 - 11:08:27 CEST