Zwei Tabellen -> mehrfache Zuordnung des Primärsch

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo zusammen,

ich habe zwei Tabellen. In einer werden die Namen der Kunden festgehalten mit der jeweiligen Kunden-ID. In einer zweiten Tabelle sind die zugehörigen Verträge der Kunden. Zu einem Vertrag gibt es mehrere Personen wie den Vertragsinhaber, Beitragszahler, versicherte Person.

Tabelle: Personendaten

id\_vn name vorname
73 Meier Franziska
71 Meier Franz
76 Meier Hans
75 Meier Test
77 Johann Siegfried
78 Johann Sepp
79 Huber Andreas

Tabelle: Vertraege (nicht vollständig)

id\_vertrag id\_vn id\_vp1 id\_vp2
1 10 10 10
2 13 13 13
3 15 16 16
6 0 0 0
9 18 18 18
10 0 0 0
11 0 0 0
13 18 18 18
14 17 17 17

Bisher hatte ich die einzelnen Schlüsseln über je eine SELECT Abfrage den jeweiligen Namen zugeordnet.

Doch leider ist mir das jetzt ein Dorn im Auge. Zur weiteren Bearbeitung und Eingabeprüfung ist eine Abfrage besser.

Wie kann ich die Zuordnung der Schlüsseln zu den Namen in einer Abfrage schaffen. Ich hatte schon Multi Joins gegoogelt, aber damit komme ich nicht klar.

Gruß

#2

Hallo Alex,

ich kann nicht ganz folgen.

Es wäre hilfreich, wenn in der Tabelle Vertraege verständliche Beispielwerte stehen würden. In jeder Zeile stehen die gleichen Werte für id_vn, id_vp1 und id_vp2. Was bedeutet das? Das jeder Vertragspartner/Vertragsperson (vp?) von sich selbst ist?

Kann es noch weitere Personen zu einem Vertrag geben? Das sieht wie eine Wiederholungsgruppe aus. So etwas sollte unbedingt vermieden werden (Verstoß gegen 1. Normalform).

Bisher hatte ich die einzelnen Schlüsseln über je eine SELECT
Abfrage den jeweiligen Namen zugeordnet.

Beispiel?

Doch leider ist mir das jetzt ein Dorn im Auge. Zur weiteren
Bearbeitung und Eingabeprüfung ist eine Abfrage besser.

Was ist der Unterschied zwischen der oben erwähnten SELECT-Abfrage und der „neuen“ Abfrage?

Wie kann ich die Zuordnung der Schlüsseln zu den Namen in
einer Abfrage schaffen. Ich hatte schon Multi Joins gegoogelt,
aber damit komme ich nicht klar.

Was soll zugeordnet werden?

Personendaten.id_vn = Vertraege.id_vn
und
Personendaten.id_vn = Vertraege.id_vp1
und
Personendaten.id_vn = Vertraege.id_vp2

in einer Abfrage?

Was soll mit der Abfrage ermittelt werden? Wie soll das Ergebnis aussehen?

Peter

#3

Moin, Alex,

die 3 ids in Vertrag sind hoffentlich die fs-Attrtibute der 3 Beziehungen, die die Rollen abbilden. Dann ist nichts gegen die Modellierung zu sagen :wink:

Die Namen kriegst Du mit

 select vertrag\_id, vn.name, vp.name, bz.name
 from person vn, person vp, person bz, vertrag
 where vn.id = vertrag.vn
 and vp.id = vertrag.vp1 
 and bz.id = vertrag.vp2

So ungefähr jedenfalls, hab grad keine Datenbank.

Gruß Ralf

#4

Melde mich wieder!
Hallo und danke für Eure Antworten!

Leider habe ich gerade keine Zeit, aber werde mich schnellstmöglich damit beschäftigen und Euch dann schreiben.

#5

Hallo Alex,

Doch leider ist mir das jetzt ein Dorn im Auge. Zur weiteren
Bearbeitung und Eingabeprüfung ist eine Abfrage besser.

pflegst du die Tabellen etwa manuell??? *kopfschüttel*

Falls ja, würde ich das an deiner Stelle lieber mit Excel machen. In Datenbanken benutzt man idealerweise Prozeduren, die Daten einfügen, aktualisieren oder löschen. Händisch werden, wenn überhaupt, nur Tabellen mit Mastereinstellungen gepflegt.

Wie kann ich die Zuordnung der Schlüsseln zu den Namen in
einer Abfrage schaffen. Ich hatte schon Multi Joins gegoogelt,
aber damit komme ich nicht klar.

Bearbeiten wirst du die Daten mit den folgenden Abfragen sicher nicht können. Zur Eingabeprüfung oder Auswertung sind die aber schon tauglich.

Möglichkeit1:

SELECT Vertraege.id_vertrag, Personendaten_1.vorname + ’ ’ + Personendaten_1.name AS vn, Personendaten_2.vorname + ’ ’ + Personendaten_2.name AS vp1,
Personendaten.vorname + ’ ’ + Personendaten.name AS vp2
FROM Personendaten AS Personendaten_1 RIGHT OUTER JOIN
Vertraege LEFT OUTER JOIN
Personendaten ON Vertraege.id_vp2 = Personendaten.id_vn LEFT OUTER JOIN
Personendaten AS Personendaten_2 ON Vertraege.id_vp1 = Personendaten_2.id_vn ON Personendaten_1.id_vn = Vertraege.id_vn

Möglichkeit2:

SELECT Vertraege.id_vertrag, ‚vn‘ AS Spalte, Personendaten.id_vn, Personendaten.vorname, Personendaten.name
FROM Vertraege LEFT OUTER JOIN
Personendaten ON Vertraege.id_vn = Personendaten.id_vn
UNION ALL
SELECT Vertraege.id_vertrag, ‚vp1‘ AS Spalte, Personendaten.id_vn, Personendaten.vorname, Personendaten.name
FROM Vertraege LEFT OUTER JOIN
Personendaten ON Vertraege.id_vp1 = Personendaten.id_vn
UNION ALL
SELECT Vertraege.id_vertrag, ‚vp2‘ AS Spalte, Personendaten.id_vn, Personendaten.vorname, Personendaten.name
FROM Vertraege LEFT OUTER JOIN
Personendaten ON Vertraege.id_vp2 = Personendaten.id_vn

MfG
Stephan

#6

Klärung
Hallo,

da hab ich ja viel Verwirrung gestiftet. Entschuldigt.

Es ist wie drambeldier gesagt hat:„die 3 ids in Vertrag sind hoffentlich die fs-Attrtibute der 3 Beziehungen, die die Rollen abbilden. Dann ist nichts gegen die Modellierung zu sagen.“

Richtig. Die Tabelle Personendaten beinhaltet die Primärschlüssel und die Tabelle Verträge beinhaltet jeweils die Fremdschlüssel.

Es handelt sich hierbei um die Erfassung von Lebensversicherungen.
Die Personen bekommen eine ID zugeordnet. Im Vertrag können dann verschiede Personen auftauchen. Beispielsweise kann der Vertragsinhaber jemand anderes sein als die 1. versicherte Person oder 2. versicherte Person.

Und ich habe hierzu ein PHP Programm erstellt, das auch die Abfragen bereits konnte, aber eben für den Vetragsinhaber und der 1. VP (versicherte Person) und der 2. VP jeweils eine gesonderte Abfrage. So erhielt ich dann die Namen zu den zugehörigen Schlüsseln aus der Tabelle Verträge und habe in der Programmmaske die Namen dann ausgeben lassen. Am Anfang genügte das mir aber jetzt ist mir aufgefallen, dass es besser wäre alles in einer Abfrage zu schaffen.

Ich habe die Abfrage von drambeldier verwendet und etwas angepasst, sodass ich zuerst mal die Werte für den VN und die 1.VP erhalte.

select id_vertrag, vn.name AS VN, vp1.name AS VP from persdaten vn, persdaten vp1, vertraege_lv where vn.id_vn = vertraege_lv.id_vn and vp1.id_vn = vertraege_lv.id_vp1

Hier das Ergebnis:

id\_vertrag VN VP
19 Meier Meier
20 Meier Meier
21 Meier Meier
26 Meier Meier
29 Meier Meier
30 Hans Hans

Leider ist dieses Ergebnis unvollständig. Es gibt wesentlich mehrere Verträge und es werden nur die Angezeigt, bei denen VN und VP gleich sind.

Die erste Abfrage von Stephan bringt folgendes Ergebnis.

id\_vertrag vn vp1 vp2
1 NULL NULL NULL
2 NULL NULL NULL
3 NULL NULL NULL
6 NULL NULL NULL
9 NULL NULL NULL
10 NULL NULL NULL
11 NULL NULL NULL
13 NULL NULL NULL
14 NULL NULL NULL
15 NULL NULL NULL
16 NULL NULL NULL
17 NULL NULL NULL
18 NULL NULL NULL
19 0 0 0
20 0 0 0
21 0 0 0
22 NULL NULL NULL
23 NULL NULL NULL
24 0 NULL NULL
26 0 0 0
27 NULL NULL NULL
28 NULL NULL NULL
29 0 0 0
30 0 0 0

Die zweite

id\_vertrag Spalte id\_vn vorname name
1 vn NULL NULL NULL
2 vn NULL NULL NULL
3 vn NULL NULL NULL
6 vn NULL NULL NULL
9 vn NULL NULL NULL
10 vn NULL NULL NULL
11 vn NULL NULL NULL
13 vn NULL NULL NULL
14 vn NULL NULL NULL
15 vn NULL NULL NULL
16 vn NULL NULL NULL
17 vn NULL NULL NULL
18 vn NULL NULL NULL
19 vn 73 Franziska Meier
20 vn 75 Hans Meier
21 vn 84 Irene Meier
22 vn NULL NULL NULL
23 vn NULL NULL NULL
24 vn 75 Hans Meier
26 vn 75 Hans Meier
27 vn NULL NULL NULL
28 vn NULL NULL NULL
29 vn 75 Hans Meier
30 vn 80 Hans Hans
1 vp1 NULL NULL NULL
2 vp1 NULL NULL NULL
3 vp1 NULL NULL NULL
6 vp1 NULL NULL NULL
9 vp1 NULL NULL NULL
10 vp1 NULL NULL NULL

Und das stimmt auch nicht. Ich muss aber auch sagen, dass ich es mir nicht so komplex vorgestellt hätte. Hut ab vor Eurem Können.

Gruß

#7

Hi Alex,

inzwischen hab ich meinen Vorschlag getestet - läuft :frowning:

Vermutlich klappt es erstmal nicht, wenn nicht alle 3 Beziehungen belegt sind, dazu müsste die Query noch erweitert werden um die jeweilige NULL. Aufwendig, aber auch kein Beinbruch.

Gruß Ralf

#8

Hallo Ralf,

du hast Recht. Jetzt ist es nur so. Für dich ist es kein Beinbruch für mich schon. Hilfst du mir bitte nochmal?

Gruß

#10

Hi,

bei dieser Anwendung bietet es sich an, alle 3 Beziehungen als Pflichtfelder zu deklarieren: Wenn VN, BZ und VP identisch sind, werden BZ und VP beim Einrichten vom VN übernommen. Und Du bist aus dem Schneider.

Gruß Ralf

#11

Leider nicht ganz so passend
Hallo nochmal,

leider ist die Idee mit den Pflichtfeldern nicht ganz so passend. Denn es gibt bei den Lebensversicherungen nicht immer zwei versicherte Personen. Also würde in den meisten Fällen das Feld leer bleiben.

Und die Datei konnte ich leider nicht öffnen.

Wenn du nochmal helfen willst hier der Befehl, wie ich ihn für mich abgeändert habe.

SELECT id\_vertrag, vn.name AS VN, vp1.name AS VP from persdaten vn, persdaten vp1, vertraege\_lv where vn.id\_vn = vertraege\_lv.id\_vn AND vp1.id\_vn = vertraege\_lv.id\_vp1

Gruß und Dank!

#13

WOW!
Super! Danke dir! Ich habe es noch nicht ausprobiert, aber auf alle Fälle viel gelernt. So wie es für mich aussieht wird die gleiche SELECT-Anweisung mehrmals durchgeführt und per UNION die Anweisung zu einer Tabelle zusammengeführt. Danke!

Ich werde mich dann nochmal melden.

Zu der Fehlermeldung:„Konnte diese Datei nicht öffnen“ Diese Datei befindet sich außerhalb Ihres Intranets oder auf einer Website, die nicht zu den vertrauenswürdigen Websites zählt. Wird diese Datei wegen möglicher Sicherheitsprobleme nicht öffnen. Um diese Datei zu öffnen, kopieren Sie sie auf Ihre Maschine oder auf einen Standort im Netzwerk, auf den Sie Zugriff haben.

Die Datei liegt aber direkt auf dem Desktop und es ist die Microsoft Access Runtime installiert. Nicht das vollwertige Access.

#14

Rückmeldung
Hallo,

also bei mir sieht es jetzt so aus. Ist nicht ganz vollständig, aber das Prinzip lässt sich erkennen.

Ich muss noch eines ergänzen. Die leeren Spalten, werde nicht mit NULL gefüllt, sondern mit 0.

So sieht dann der Befehl aus.

SELECT id\_vertrag, vn.name AS VN, vp1.name AS VP from persdaten vn, persdaten vp1, vertraege\_lv
where vn.id\_vn = vertraege\_lv.id\_vn AND
vp1.id\_vn = vertraege\_lv.id\_vp1
UNION
SELECT id\_vertrag, vn.name AS VN, 0 AS VP from persdaten vn, persdaten vp1, vertraege\_lv where vn.id\_vn = vertraege\_lv.id\_vn AND vertraege\_lv.id\_vp1 = 0

Aber was bedeutet in diesem Befehl „0 AS VP“? Für gewöhnlich wird hier ein Alias definiert. Aber 0 ist ja kein Spaltenname oder Tabellenname?

Gruß

#15

Ich muss noch eines ergänzen. Die leeren Spalten, werde nicht
mit NULL gefüllt, sondern mit 0.

0 ist nicht Nichts, Nichts ist Null :smile:))

Schau mal bei der Spaltendefinition nach, ob als Vorbelegung 0 angegeben ist, das muss raus. Sonst könnte die 0 nämlich in schwachen Momenten als Verweis auf die Person mit der Id=0 angesehen werden.

Aber was bedeutet in diesem Befehl „0 AS VP“?

Beim Union müssen alle Teile die gleichen Spaltennamen haben, das wird erreicht durch Zuweisung des Nullwertes auf den jeweiligen Namen. Nicht NULL ist der Alias, sondern VP.

Gruß Ralf

#16

hmm…
Also herzlichen Dank! Der Befehl funktioniert soweit, aber dennoch ist mir der Teil „0 AS VP“ nicht ganz klar.

Ich habe diesen Teil einfach mal durch „1 AS VP“ ersetzt oder einfach mal nur eine Zahl reingeschreiben und „AS VP“ weggelassen oder einfach nur 0 reingeschrieben und „AS VP“ und trotzdem kam nie eine Fehlermeldung. Die Abfrage wurde dennoch durchgeführt. Ich dache immer, dass ein Alias nur dann funktioniert, wenn auch die Spalte, auf die der Alias gesetzt wird tatsächlich existieren. Aber wenn ich z.B. 5 AS VP reinschreibe, dann handelt es sich bei der 5 nicht um eine tatsächlich existierende Spalte und trotzdem wird die Abfrage gemacht. Oder auch wenn ich nur 5 ohne „AS VP“ reinschreibe klappt die Abfrage.

Wie kann das sein?

#17

Hi,

Wie kann das sein?

der Union kennt keine Spaltennamen, sondern nur die Position in der Ergebnisleiste. Ähnlich geht’s beim SORT: Da kann jederzeit anstelle des Namens die Position mitgegeben werden, also zB order by 1, 4, 2, 7.

Gruß Ralf

#18

Danke!
Hallo,

also danke nochmal für deine Hilfe!