Re: dumbheaded SQL question (probably join or subselect) - Mailing list pgsql-general
From | Mike Mascari |
---|---|
Subject | Re: dumbheaded SQL question (probably join or subselect) |
Date | |
Msg-id | 3F361CC1.5040607@mascari.com Whole thread Raw |
In response to | dumbheaded SQL question (probably join or subselect) - longish (Karsten Hilbert <Karsten.Hilbert@gmx.net>) |
Responses |
Re: dumbheaded SQL question (probably join or subselect)
(Karsten Hilbert <Karsten.Hilbert@gmx.net>)
|
List | pgsql-general |
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 mascarm@mascari.com
pgsql-general by date: