Advanced(?) query question

#1

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

#2

"Anders Steinlein" <dinhelt (AT) online (DOT) noSPAM> wrote

Quote:

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?

Yes.

#3

In article <ZDuab.216$uO1.20255791 (AT) mantis (DOT) golden.net>, bbadour (AT) golden (DOT) net
says...

Quote:

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

#4

"Anders Steinlein" <dinhelt (AT) online (DOT) noSPAM> wrote

Quote:

In article <ZDuab.216$uO1.20255791 (AT) mantis (DOT) golden.net>, bbadour (AT) golden (DOT) 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?

Use a scalar correlated subquery and the count aggregate function.
Alternatively, use both "group by" and "having" clauses.

#5

"Anders Steinlein" <dinhelt (AT) online (DOT) noSPAM> wrote

Quote:

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

Regards,
jag

#6

"John Gilson" <jag (AT) acm (DOT) org> wrote

Quote:

"Anders Steinlein" <dinhelt (AT) online (DOT) noSPAM> wrote in message
news:MPG.19d4790bf8c57019896dd (AT) news (DOT) 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?

#7

Anders Steinlein <dinhelt (AT) online (DOT) noSPAM> wrote

Quote:

In article <ZDuab.216$uO1.20255791 (AT) mantis (DOT) golden.net>, bbadour (AT) golden (DOT) 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

#8

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

Quote:

"John Gilson" <jag (AT) acm (DOT) org> wrote in message
news:ZWuab.6834$nU6.1269431 (AT) twister (DOT) nyc.rr.com...
"Anders Steinlein" <dinhelt (AT) online (DOT) noSPAM> wrote in message
news:MPG.19d4790bf8c57019896dd (AT) news (DOT) 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?

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

#9

"John Gilson" <jag (AT) acm (DOT) org> wrote

Quote:

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

"John Gilson" <jag (AT) acm (DOT) org> wrote in message
news:ZWuab.6834$nU6.1269431 (AT) twister (DOT) nyc.rr.com...
"Anders Steinlein" <dinhelt (AT) online (DOT) noSPAM> wrote in message
news:MPG.19d4790bf8c57019896dd (AT) news (DOT) 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?

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.

#10

In article <W_wab.235$9e2.21597243 (AT) mantis (DOT) golden.net>, bbadour (AT) golden (DOT) net
says...

Quote:

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?

Oh, ok, so you just assume this was a homework assignment? Why? Cause no,
it is not a homework assignment, but part of a project for my own
company. Sure, I may not be an expert in SQL, but that shouldn't
automatically translate to it being a homework assignment, should it?

\Anders