Bestimmt ganz einfach - PLZ in der nähe von PLZ

sven.mitternach

Benutzer
Beiträge
5
Hallo,

ich habe hier eine Datenbank mit zwei Tabellen. Tabelle 1 sind Kundendaten, Tabelle 2 sind Fachhändleradressen. Ich möchte nun für einen Serienbrief einfach eine View haben, die in einer Zeile die komplette Kundenandresse enthält und insgesamt IMMER 5 Fachhändler in der Nähe der PLZ des Kunden.

Für jemanden der da Ahnung von hat ist das wahrscheinlich ganz einfach.

Tabellenaufbau Kunden
Titel
Vorname
Nachname
Strasse
Hausnummer
PLZ
Ort

Tabellenaufbau Händler
Name
PLZ
Ort
Strasse

Vielen Dank schon einmal für eure schnell Hilfe.
 
Werbung:

Charly

Datenbank-Guru
Beiträge
306
AW: Bestimmt ganz einfach - PLZ in der nähe von PLZ

Hallo,

Händler mit der gleichen PLZ wie Kunde oder auch Händler die PLZ in der nähe von Kunde PLZ haben?

Gruß Charly
 

sven.mitternach

Benutzer
Beiträge
5
AW: Bestimmt ganz einfach - PLZ in der nähe von PLZ

Das wäre ganz gut. Als erstes sollten natürlich die Einträge mit exakt gleicher PLZ kommen und dann die mit der niedrigsten Differenz.
 

Charly

Datenbank-Guru
Beiträge
306
AW: Bestimmt ganz einfach - PLZ in der nähe von PLZ

Hallo ,
ich denke Er meint die räumliche Nähe. Dazu braucht man Geodaten zu den PLZ.

Die bekommt man z.B. von opengeodb. Das wird aber ein bisschen komlpexer.

Gruß Charly
 

thomas_w

SQL-Guru
Beiträge
104
AW: Bestimmt ganz einfach - PLZ in der nähe von PLZ

So könnte die Berechnung bei einer reiner PLZ Differenzberechnung aussehen. Die Abfrage gilt immer für einen Kunden, also z.B. Kunde =1. Gesucht wird in einem Bereich von +/- 10000 PLZ.

Das ganze mit MySQL, obwohl es hier das MS-SQL Forum ist.

Code:
CREATE TABLE kunde (
 id INT NOT NULL,
 name VARCHAR(50) NOT NULL,
 plz INT NOT NULL
);


CREATE TABLE haendler (
 id INT NOT NULL,
 name VARCHAR(50) NOT NULL,
 plz INT NOT NULL
);

INSERT INTO kunde VALUES
( 1, 'Name-1', 20100),
( 2, 'Name-2', 30100),
( 3, 'Name-3', 40100);

INSERT INTO haendler VALUES
( 1, 'H-1', 20100),
( 2, 'H-2', 30100),
( 3, 'H-3', 40100),
( 4, 'H-4', 20200),
( 5, 'H-5', 30200),
( 6, 'H-6', 40200),
( 7, 'H-7', 50500),
( 8, 'H-8', 60500),
( 9, 'H-9', 70200);

INSERT INTO haendler VALUES
(10, 'H-10', 10100),
(11, 'H-11', 15100),
(12, 'H-12', 45100),
(13, 'H-13', 25200),
(14, 'H-14', 35200),
(15, 'H-15', 45200),
(16, 'H-16', 55500),
(17, 'H-17', 65500),
(18, 'H-18', 75200);


/* Ergebnis für Kunde 1*/
SELECT k.id, k.name, k.plz, h.id, h.name, h.plz, nah.plz_diff
  FROM haendler h
  JOIN (SELECT h.id, ABS ( k.plz - h.plz ) plz_diff
          FROM kunde k,
               haendler h
         WHERE k.plz BETWEEN h.plz -10000 AND h.plz + 10000
           AND k.id = 1
         ORDER BY ABS ( k.plz - h.plz ) ASC
         LIMIT 5
       ) nah
    ON nah.id = h.id
  JOIN kunde k
    ON k.id = 1
ORDER BY nah.plz_diff;

+----+--------+-------+----+------+-------+----------+
| id | name   | plz   | id | name | plz   | plz_diff |
+----+--------+-------+----+------+-------+----------+
|  1 | Name-1 | 20100 |  1 | H-1  | 20100 |        0 |
|  1 | Name-1 | 20100 |  4 | H-4  | 20200 |      100 |
|  1 | Name-1 | 20100 | 11 | H-11 | 15100 |     5000 |
|  1 | Name-1 | 20100 | 13 | H-13 | 25200 |     5100 |
|  1 | Name-1 | 20100 |  2 | H-2  | 30100 |    10000 |
+----+--------+-------+----+------+-------+----------+
5 rows in set (0.00 sec)

mysql>
Vielleicht hilft es ja ein Stück weiter oder jemand hat eine bessere Lösung.

Grüße
Thomas
 

Charly

Datenbank-Guru
Beiträge
306
AW: Bestimmt ganz einfach - PLZ in der nähe von PLZ

Hallo,

einfach die Zeile

Code:
LIMIT 5


entfernen und dann die Zeile
Code:
JOIN (SELECT h.id, ABS ( k.plz - h.plz ) plz_diff

durch die Zeile
Code:
JOIN (SELECT TOP 5 h.id, ABS ( k.plz - h.plz ) plz_diff
ersetzen.

Gruß Charly
 
Werbung:

Charly

Datenbank-Guru
Beiträge
306
AW: Bestimmt ganz einfach - PLZ in der nähe von PLZ

Hallo,

gelöst hätte ich das ja am liebsten mit einer Funktion.

Da ich aber nicht genau weis was Word als Datenquelle alles akzeptiert habe ich die folgende SELECT-Anweisung verbrochen.:D

Code:
SELECT DISTINCT K1.*
,H1.Haendler As Haendler1
,H2.Haendler as Haendler2
,H3.Haendler as Haendler3
,H4.Haendler as Haendler4
,H5.Haendler as Haendler5
FROM Kunde as K1 
JOIN  
 (
 SELECT DISTINCT ku.id as id, ha.plz as Haendler FROM kunde as ku,haendler as ha
  WHERE ha.plz =
  (
   SELECT MAX(HA1.plz) as plz FROM
   (
    SELECT top 5 h.plz
    FROM kunde as k,haendler as h
    WHERE k.plz BETWEEN h.plz -10000 AND h.plz + 10000
    and k.plz = ku.plz
    order by h.plz
   )as HA1
  )
 ) AS H1
ON K1.ID=H1.ID
JOIN
 (
 SELECT DISTINCT ku.id as id,ha.plz as Haendler FROM kunde as ku,haendler as ha
 WHERE ha.plz =
  (
   SELECT MAX(HA2.plz)as plz FROM
    (
    SELECT top 4 h.plz
    FROM kunde as k,haendler as h
    WHERE k.plz BETWEEN h.plz -10000 AND h.plz + 10000
    and k.plz = ku.plz
    order by h.plz
    ) as HA2
   ) 
  ) AS H2
ON K1.ID=H2.ID
JOIN
 (
 SELECT ku.id as id,ha.plz as Haendler FROM kunde as ku,haendler as ha
  WHERE ha.plz =
   (
   SELECT MAX(HA3.plz) FROM
    (
    SELECT top 3 h.plz
    FROM kunde as k,haendler as h
    WHERE k.plz BETWEEN h.plz -10000 AND h.plz + 10000
    and k.plz = ku.plz
    order by h.plz
    ) as HA3
   ) 
  ) AS H3
ON K1.ID=H3.ID
JOIN
 (
 SELECT ku.id as id,ha.plz as Haendler FROM kunde as ku,haendler as ha
  WHERE ha.plz =
   (
   SELECT MAX(HA4.plz)as plz FROM
    (
    SELECT top 2 h.plz
    FROM kunde as k,haendler as h
    WHERE k.plz BETWEEN h.plz -10000 AND h.plz + 10000
    and k.plz = ku.plz
    order by h.plz
    )as HA4
   ) 
  ) AS H4
ON K1.ID=H4.ID
JOIN
 (
 SELECT ku.id as id,ha.plz as Haendler FROM kunde as ku,haendler as ha
  WHERE ha.plz =
   (
    SELECT top 1 h.plz
    FROM kunde as k,haendler as h
    WHERE k.plz BETWEEN h.plz -10000 AND h.plz + 10000
    and k.plz = ku.plz
    order by h.plz
   ) 
  ) AS H5
ON K1.ID=H5.ID

Ich habs auch mit PIVOT-Funktionen versucht, bin da aber zu keinem befriedigendem Ergebnis gekommen.

Es ist auch ein bisschen bei Thomas geklaut.

Es gibt noch ein paar Fälle bei denen das Ergebnis nicht sauber ist.
Beispielsweise wenn weniger als 5 Händler pro Kunde gefunden werden.

Ich wollte das ganze aber nicht noch unübersichtlicher machen.


Ergebnis mit den Testdaten von Thomas:

Code:
ID Name    PLZ       Haendler1 Haendler2 Haendler3 Haendler4 Haendler5
1  Name-1  20100     25200     20200     20100     15100     10100
2  Name-2  30100     30200     30100     25200     20200     20100
3  Name-3  40100     40200     40100     35200     30200     30100

Ich habe das jetzt nicht bis in Detail geprüft aber es dürfte so halbwegs laufen.

Gruß Charly
 
Oben