dumbheaded SQL question (probably join or subselect) - longish

От:

Karsten Hilbert <Karsten.Hilbert@gmx.net>

Дата:

10 августа 2003 г. в 05:02:34

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

Re: dumbheaded SQL question (probably join or subselect)

От:

Karsten Hilbert <Karsten.Hilbert@gmx.net>

Дата:

10 августа 2003 г. в 12:20:12

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

Re: dumbheaded SQL question (probably join or subselect)

От:

Mike Mascari <mascarm@mascari.com>

Дата:

10 августа 2003 г. в 07:22:57

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