dumbheaded SQL question (probably join or subselect) - longish
От | Karsten Hilbert |
---|---|
Тема | dumbheaded SQL question (probably join or subselect) - longish |
Дата | |
Msg-id | 20030810100034.F563@hermes.hilbert.loc обсуждение исходный текст |
Ответы |
Re: dumbheaded SQL question (probably join or subselect)
(Mike Mascari <mascarm@mascari.com>)
|
Список | 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
В списке pgsql-general по дате отправления:
Следующее
От: Mike MascariДата:
Сообщение: Re: dumbheaded SQL question (probably join or subselect)