dumbheaded SQL question (probably join or subselect) - longish - Mailing list pgsql-general

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