dumbheaded SQL question (probably join or subselect) - longish

[Karsten Hilbert]

Dear all,

for some reason I just cannot get my brain wrapped around the
required syntax for the following. I think I need to either
use a join or subselect(s):

Situation:
----------
I have two tables (simplified here) for an international
medical office application (www.gnumed.org):

create table city (
id serial primary key,
postcode text,
name text
);

create table street (
id serial primary key,
id_city integer references city(id),
postcode text,
name text
);

Yes, postcode is in both tables by design:

e.g. in Germany postcodes can be valid for:
- several smaller "towns"
- one "town"
- several streets in one "town"
- one street in one "town"
- part of one street in one "town"

Problem:
--------
I want to create a view v_zip2data that lists:

- all zip codes from "street" with associated data
- all those zip codes in "city" that are not in "street" OR
that belong to a different city name in "street"
- and from both tables only those rows that do have a zip code

insert into city (id, postcode, name) values (1, '02999', 'Gross Saerchen');
insert into city (id, postcode, name) values (2, '02999', 'Lohsa');
insert into city (id, postcode, name) values (3, '04318', 'Leipzig');
insert into city (id, postcode, name) valueus (4, '06686, 'Luetzen');
insert into city (id, name) values (5, 'Leipzig');

insert into street (id_city, name) values (1, 'No-ZIP street');
insert into street (id_city, postcode, name) values (2, '02999', 'Main Street');
insert into street (id_city, postcode, name) values (3, '04217', 'Riebeckstrasse');
insert into street (id_city, postcode, name) values (5, '04318', 'Zum Kleingartenpark');
insert into street (id_city, postcode, name) values (6, '04318', 'Wurzener Strasse');

I want to see in the view:

(from street)
02999, Main Street, Lohsa
04217, Riebeckstrasse, Leipzig
- city.postcode ignored and overridden
04318, Zum Kleingartenpark, Leipzig
04318, Wurzener Strasse, Leipzig
- same zip/city but different street
(from city)
02999, NULL, Gross Saerchen
- zip is in "street" but points to city "Lohsa"
06686, NULL, Luetzen
- zip not listed in "street"

I want to exclude from the view:
- city.id=2 since that is covered by the second "street" row
- city.id=3 since that is covered by the fourth "street" row
- city.id=5 since that does not have a zip code
- first row in "street" since it does not have a zip code

I have been trying to join city and street on "city.postcode <>
street.postcode" in various ways but was unable to achieve the
view I wanted. Same with using subselects in the where clause
(NOT IN ... which is supposed to be of suboptimal performance
IIRC). A first step would be to have a view listing all zips
from "city" that satisfy:

- not listed in "street" OR
- listed in "street" but street.id_city points to a different city

Any help would be appreciated.

Thanks,
Karsten Hilbert, MD
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Mike Mascari]

Karsten Hilbert wrote:

Dear all,

for some reason I just cannot get my brain wrapped around the
required syntax for the following. I think I need to either
use a join or subselect(s):

Situation:
----------
I have two tables (simplified here) for an international
medical office application (www.gnumed.org):

create table city (
id serial primary key,
postcode text,
name text
);

create table street (
id serial primary key,
id_city integer references city(id),
postcode text,
name text
);

Yes, postcode is in both tables by design:

e.g. in Germany postcodes can be valid for:
- several smaller "towns"
- one "town"
- several streets in one "town"
- one street in one "town"
- part of one street in one "town"

Problem:
--------
I want to create a view v_zip2data that lists:

- all zip codes from "street" with associated data
- all those zip codes in "city" that are not in "street" OR
that belong to a different city name in "street"
- and from both tables only those rows that do have a zip code

insert into city (id, postcode, name) values (1, '02999', 'Gross Saerchen');
insert into city (id, postcode, name) values (2, '02999', 'Lohsa');
insert into city (id, postcode, name) values (3, '04318', 'Leipzig');
insert into city (id, postcode, name) valueus (4, '06686, 'Luetzen');
insert into city (id, name) values (5, 'Leipzig');

insert into street (id_city, name) values (1, 'No-ZIP street');
insert into street (id_city, postcode, name) values (2, '02999', 'Main Street');
insert into street (id_city, postcode, name) values (3, '04217', 'Riebeckstrasse');
insert into street (id_city, postcode, name) values (5, '04318', 'Zum Kleingartenpark');
insert into street (id_city, postcode, name) values (6, '04318', 'Wurzener Strasse');

I want to see in the view:

(from street)
02999, Main Street, Lohsa
1:

SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city AND
city.postcode = street.postcode
04217, Riebeckstrasse, Leipzig
- city.postcode ignored and overridden
2:

SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city AND
city.postcode <> street.postcode
04318, Zum Kleingartenpark, Leipzig
3:

SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city AND
city.postcode IS NULL
04318, Wurzener Strasse, Leipzig
- same zip/city but different street
4:

SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.postcode = street.postcode AND
NOT EXISTS (
SELECT 1
FROM city c
WHERE street.id_city = c.id
)
(from city) 02999, NULL, Gross Saerchen
- zip is in "street" but points to city "Lohsa"
5:

SELECT city.postcode, NULL, city.name
FROM city
WHERE NOT EXISTS (
SELECT 1
FROM street
WHERE city.id = street.id_city AND
city.postcode = street.postcode AND
) AND
city.postcode IS NOT NULL
06686, NULL, Luetzen
- zip not listed in "street"
6:

SELECT city.postcode, NULL, city.name
FROM city
WHERE NOT EXISTS (
SELECT 1
FROM street
WHERE city.postcode = street.postcode
)
city.postcode IS NOT NULL

----

Now, all you need to do is unionize these selects:

CREATE VIEW v_zip2data AS
SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city AND
city.postcode = street.postcode
UNION
SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city AND
city.postcode <> street.postcode
UNION
SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city AND
city.postcode IS NULL
UNION
SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.postcode = street.postcode AND
NOT EXISTS (
SELECT 1
FROM city c
WHERE street.id_city = c.id
)
UNION
SELECT city.postcode, NULL, city.name
FROM city
WHERE NOT EXISTS (
SELECT 1
FROM street
WHERE city.id = street.id_city AND
city.postcode = street.postcode AND
) AND
city.postcode IS NOT NULL
UNION
SELECT city.postcode, NULL, city.name
FROM city
WHERE NOT EXISTS (
SELECT 1
FROM street
WHERE city.postcode = street.postcode
) AND
city.postcode IS NOT NULL;

Please verify each of the selects that compose the view. The UNION
will eliminate any redundancies, so some of the SELECTs may be able to
be logically combined:

1, 2 and 3 appear to be, logically:

SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city

5 and 6 appear to be, logically:

SELECT city.postcode, NULL, city.name
FROM city
WHERE NOT EXISTS (
SELECT 1
FROM street
WHERE city.id = street.id_city
) AND
city.postcode IS NOT NULL;

so that reduces the view definition to:

CREATE VIEW v_zip2data AS
SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.id = street.id_city
UNION
SELECT street.postcode, street.name, city.name
FROM city, street
WHERE city.postcode = street.postcode AND
NOT EXISTS (
SELECT 1
FROM city c
WHERE street.id_city = c.id
)
SELECT city.postcode, NULL, city.name
FROM city
WHERE NOT EXISTS (
SELECT 1
FROM street
WHERE city.id = street.id_city
) AND
city.postcode IS NOT NULL;
Any help would be appreciated.

Thanks,
Karsten Hilbert, MD

Of course, I could (easily) be misunderstanding the nature of the
data, but it should be a starting point. If you consider normalizing
further, here's a good paper to aid you on the restructuring: ;-)

http://home.earthlink.net/~billkent/Doc/simple5.htm

HTH,

Mike Mascari
ma*****@mascari.com




---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Karsten Hilbert]

UNION. Duh. I knew why I wrote to this list :-)

Thanks Mike.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html