Re: dumbheaded SQL question (probably join or subselect)

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(at)mascari(dot)com