kniffliges Abfrage Problem

[Jaraz]

Hi,

Lösung soll unter Oracle und Mysql laufen, also keine Subselects bitte.

Ich habe 3 Tabellen


Tabelle Verein

----------------------

|int         |vid    |

|varchar2(32)|name   |

|int         |pruefer| <- Fremdschlüssel (pid) aus Tabelle Person

----------------------


Tabelle Person

-------------------

|int         |pid |

|varchar2(32)|name|

-------------------


Tabelle Termin

--------------------

|int         |vid  |

|varchar2(32)|datum|

|varchar2(32)|topic|

--------------------


eine vierte Tabelle ist für die n:m Beziehung zwischen Verein und Person zuständig.


Tabelle Mitglieder

--------------------

|int         |vid  |

|int         |pid  |

--------------------

Eine Person kann also Mitglied und Pruefer bei einem Verein sein. Ein Pruefer muss aber nicht zwingend Mitglied sein. Ich benötige nun zu einer bestimmten Person alle Vereine und jeweils den Pruefer des Vereins(egal ob die Person Pruefer oder Mitglied oder beides ist) mit allen Terminen.

Tabelle Verein

-------------

|1|BVB    |1|

|2|Hertha |1|

|3|Schalke|2|

|4|Werder |3|

-------------


Tabelle Person

---------

|1|Willi|

|2|Klaus|

|3|Peter|

---------


Tabelle Termin

-------------------------------

|1|01.02.2001|Hauptversammlung|

|1|02.08.2002|Jahresabschluss |

|2|01.01.2001|Hauptversammlung|

|2|02.08.2002|Jahresabschluss |

|3|01.03.2001|Hauptversammlung|

|3|02.08.2002|Jahresabschluss |

|4|02.08.2002|Hauptversammlung|

-------------------------------


Tabelle Mitglieder(Nur Willis Datensätze)

-----

|1|1|

|2|1|

-----

Person Willi ist also Pruefer und Mitglied beim BVB, nur Pruefer bei Hertha, nur Mitglied bei Schalke und nix bei Werder. Demensprechend sollte das Abfrageergebniss bei Willi so lauten:

----------------------------

|Verein |Pruefer|Termin    |

----------------------------

|BVB    |Willi  |01.02.2001|

|BVB    |Willi  |02.08.2002|

|Hertha |Willi  |01.01.2001|

|Hertha |Willi  |02.08.2002|

|Schalke|Klaus  |01.03.2001|

|Schalke|Klaus  |02.08.2002|

----------------------------

Ich bekomme das nur über ein Subselect hin, hätte es aber gerne ohne, da Mysql das nicht unterstützt.

Das Problem ist die doppelte Verknüpfung zwischen Verein und Person, einmal direkt als Prüfer und einmal über die Tabelle Mitglieder.

Gruß Jaraz

[Der Kleine]

Äh, also du willst aufbauend auf eine (virtuelle) Abfrage eine zweite Abfrage ausführen und deren Ergebnisse anzeigen lassen? So wie Access das schon immer vorschlägt? (ohne Subselect in einer Abfrage).

Hoffe es hilft!

[Jaraz]

So ungefähr, wobei die virtuelle Abfrage über Joins aufgelöst werden soll.

Dump der Datenbank, falls das jemand praktisch nachvollziehen will.

CREATE TABLE mitglieder (

  pid int(11) default NULL,

  vid int(11) default NULL

) TYPE=MyISAM;


INSERT INTO mitglieder VALUES (1,1);

INSERT INTO mitglieder VALUES (2,1);


CREATE TABLE person (

  pid int(11) default NULL,

  name varchar(30) default NULL

) TYPE=MyISAM;


INSERT INTO person VALUES (1,'Willi');

INSERT INTO person VALUES (2,'Klaus');

INSERT INTO person VALUES (3,'Peter');


CREATE TABLE termin (

  vid int(11) default NULL,

  datum varchar(30) default NULL,

  art varchar(30) default NULL

) TYPE=MyISAM;


INSERT INTO termin VALUES (1,'1.2','xxx');

INSERT INTO termin VALUES (1,'1.8','yyy');

INSERT INTO termin VALUES (2,'1.2','xxx');

INSERT INTO termin VALUES (2,'1.8','yyy');

INSERT INTO termin VALUES (3,'1.2','xxx');

INSERT INTO termin VALUES (3,'1.8','yyy');

INSERT INTO termin VALUES (4,'1.2','xxx');


CREATE TABLE verein (

  vid int(11) default NULL,

  name varchar(30) default NULL,

  pruefer int(11) default NULL

) TYPE=MyISAM;


INSERT INTO verein VALUES (1,'BVB',1);

INSERT INTO verein VALUES (2,'Hertha',1);

INSERT INTO verein VALUES (3,'Schalke',2);

INSERT INTO verein VALUES (4,'Werder',3);

[Jaraz]

Sorry, im Dump waren Fehler.

Hier nun die schon mal funktionierende Lösung.

CREATE TABLE mitglieder (

  pid int(11) default NULL,

  vid int(11) default NULL

) TYPE=MyISAM;


INSERT INTO mitglieder VALUES (1,1);

INSERT INTO mitglieder VALUES (1,3);


CREATE TABLE person (

  pid int(11) default NULL,

  name varchar(30) default NULL

) TYPE=MyISAM;


INSERT INTO person VALUES (1,'Willi');

INSERT INTO person VALUES (2,'Klaus');

INSERT INTO person VALUES (3,'Peter');


CREATE TABLE termin (

  vid int(11) default NULL,

  datum varchar(30) default NULL,

  topic varchar(30) default NULL

) TYPE=MyISAM;


INSERT INTO termin VALUES (1,'1.2','xxx')

INSERT INTO termin VALUES (1,'1.8','yyy')

INSERT INTO termin VALUES (2,'1.2','xxx')

INSERT INTO termin VALUES (2,'1.8','yyy')

INSERT INTO termin VALUES (3,'1.2','xxx')

INSERT INTO termin VALUES (3,'1.8','yyy')

INSERT INTO termin VALUES (4,'1.2','xxx')


CREATE TABLE verein (

  vid int(11) default NULL,

  name varchar(30) default NULL,

  pruefer int(11) default NULL

) TYPE=MyISAM;


INSERT INTO verein VALUES (1,'BVB',1);

INSERT INTO verein VALUES (2,'Hertha',1);

INSERT INTO verein VALUES (3,'Schalke',2)

INSERT INTO verein VALUES (4,'Werder',3);

Folgendes Statement liefert unter Mysql das gewünschte Ergebnis. Ich hoffe das funktioniert auch unter Oracle, kann ich leider im Moment nicht testen.

select distinct verein.name, tperson.name as pruefer, termin.datum

 from verein, mitglieder, person

 inner join termin on termin.vid = verein.vid

 inner join person as tperson on tperson.pid = verein.pruefer

 where (verein.pruefer = person.pid or mitglieder.vid = verein.vid) and person.pid = 1;


+---------+---------+-------+

| name    | pruefer | datum |

+---------+---------+-------+

| BVB     | Willi   | 1.2   |

| BVB     | Willi   | 1.8   |

| Hertha  | Willi   | 1.2   |

| Hertha  | Willi   | 1.8   |

| Schalke | Klaus   | 1.2   |

| Schalke | Klaus   | 1.8   |

+---------+---------+-------+

Falls jemand weiss, wie man den cross join der 3 Tabellen verein, mitglieder, person (den ich für die where Bedingung brauche) eleganter schreiben kann, würde ich mich freuen.

Gruß Jaraz

[sweetie110]

Als ich verstehe hier nicht worum es gehen soll. Kannst du vielleicht mal die Kriterien nach denen gefragt wirst kurz auflisten und zusammenfassen? Und evt. dein Datenmodell erläutern?

[Jaraz]

Originally posted by sweetie110

Als ich verstehe hier nicht worum es gehen soll. Kannst du vielleicht mal die Kriterien nach denen gefragt wirst kurz auflisten und zusammenfassen? Und evt. dein Datenmodell erläutern?

Ich hoffe da fehlt irgendwo der Ironie Tag. Datenmodell ist doch wohl ausführlichst beschrieben.

Die Abfrage ist soweit auch erst mal fertig es wäre jetzt nur noch performance-technisch wünschenswert den cross join der 3 Tabellen (verein, mitglieder, person) der für die where Bedingung benötigt wird, zu vermeiden.

Gruß Jaraz

[Panther]

ok hier ein Versuch ohne Test und aus dem Ärmel :

deins :

select distinct verein.name, tperson.name as pruefer, termin.datum

from verein, mitglieder, person

inner join termin on termin.vid = verein.vid

inner join person as tperson on tperson.pid = verein.pruefer

where (verein.pruefer = person.pid or mitglieder.vid = verein.vid) and person.pid = 1;

mein Vorschlag :

select distinct v.name , tp.name , t.datum,

from verein as v, mitglieder as m, person as p , person as tp, termin as t

where (t.vid=v.vid) and (tp.pid=v.pruefer) and (v.pruefer = p.pid or m.vid = v.vid) and (p.pid = 1);

[Jaraz]

Originally posted by Panther

ok hier ein Versuch ohne Test und aus dem Ärmel :

Womit du genau das Gegenteil bewirkst, indem du den cross join über 5 anstatt 3 Tabellen gegen lässt.

Der Speicherverbrauch wird noch größer und die Query damit langsamer.

Gruß Jaraz

[Panther]

dein join geht doch auch über 5 Tabellen.

könnte sein das die beiden Statements gleich schnell sind.

musst du am besten mal ausprobieren.

[Jaraz]

Originally posted by Panther

dein join geht doch auch über 5 Tabellen.

Ja, ich frage mich nur gerade, ob bei deiner Schreibweise erst das komplette Kreuzprodukt gebildet wird, und dann die where Klauseln als Einschränkung benutzt werden, oder ob die Datenbank direkt so schlau ist, und beide Schreibweisen gleich behandelt.

Leider habe ich im Moment nur so wenig Testdaten, das sich kein Unterschied herausfinden lässt.

Gruß Jaraz

[Jaraz]

Also, laut EXPLAIN wird zumindest bei Mysql beides gleich behandelt. Es ist halt nur wichtig, das für jede Tabelle eine Einschränkung vorhanden ist ob dies per INNER JOIN xxx ON YYY oder als where Klausel passiert, ist anscheinend egal.

Der Mysql interne Query Optimizer analysiert sogar die Tabellengrößen, sodaß die Reihenfolge der Tabellenangaben egal sein sollte.

Na ja, wieder was gelernt.

Das Problem hat sich also erledigt. Ich habe mein Ergebnis und gut ist.

Danke an alle die sich damit beschäftigt haben.

Gruß Jaraz

[Panther]

mmhh.. das ist doch mal was.

Naja wahrscheinlich wird das aber auch wieder von DB zu DB anders sein.

Und wenn man nciht genug Testdaten hat wird es schwierig da ne 100%ige Aussage zu machen ....

Naja hast es ja auch selbst gelöst.

Greetzt

Panther