verwickelte SQL-Abfrage mit JOIN's

[Ulrich Kretschmer]

Hallo,

Am Dienstag 22. Mai 2007 11:35 schrieb andreas balke:
> leider kann ich das komplette statement nicht bauen, da die anderen
> tabellen fehlen. 
Die kompletten Tabellendefinitionen habe ich an den Schluß angehängt. (Ich 
hatte zunächst darauf verzichtet, da ziemlich umfangreich.)

Um gleich der nächsten Frage zuvorzukommen - nein, ich habe diese Anwendung 
nicht programmiert. Ich versuche nur, die mich im Moment speziell 
interessierenden Daten aus der Datenbank herauszuziehen...

> ich nehme an, das hier ist die tng_people. meiner 
> meinung nach solltest du diese tabelle einfach doppelt rein joinen:

hm.... am besten schrittweise:

1) Ergebnisse meiner ursprünglichen Abfrage, beschränkt auf einige typische 
Beispieldaten:
http://www.ahnendaten.de/ahnen/showreport.php?reportID=223
(Das PHP-Skript übersetzt die ursprünglichen Spaltenbezeichnungen z.T. ins 
Deutsche.)

2) Nun eine Weiterentwicklung mit doppelten Joins:

SELECT familyID, w.personID, w.lastname, w.firstname, et.tag, et.description, 
e.info, et2.tag, et2.description, e2.info FROM tng_families AS f 
INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) 
LEFT JOIN tng_events AS e ON (w.personID=e.persfamID AND w.gedcom=e.gedcom)
LEFT JOIN tng_eventtypes AS et ON (e.eventtypeID=et.eventtypeID)
LEFT JOIN tng_people AS w2 ON (f.wife=w2.personID AND f.gedcom=w2.gedcom)
LEFT JOIN tng_events AS e2 ON (w.personID=e2.persfamID AND w.gedcom=e2.gedcom)
LEFT JOIN tng_eventtypes AS et2 ON (e2.eventtypeID=et2.eventtypeID);

wobei mir das etwas spanisch vorkommt, weil manche Personenzeilen verdoppelt 
werden (ich will nicht behaupten, daß ich das wirklich verstehe); 
Beispiel-Ergebnisse:
http://www.ahnendaten.de/ahnen/showreport.php?reportID=224

> LEFT JOIN tng_people AS wExclude ON (f.wife=w.personID AND
> f.gedcom=w.gedcom AND et.tag="EVEN" AND description="Ehename") INNER JOIN
> tng_people AS wInclude ON (f.wife=w.personID AND f.gedcom=w.gedcom AND ( (
> et.tag IS NOT NULL AND description<>"Ehename" )
>     AND !( et.tag="EVEN" AND description="Ehename" ) /* überflüssig, aber
> sicher ist sicher ;) */ )
> )

3) Nun habe ich versucht, die ergänzenden Bedingungen einzubauen (ob ich es 
aber richtig gemacht habe, ist zweifelhaft):

SELECT familyID, w.personID, w.lastname, w.firstname, et.tag, et.description, 
e.info, w2.personID, et2.tag, et2.description, e2.info FROM tng_families AS f 
INNER JOIN tng_people AS w ON (f.wife=w.personID AND f.gedcom=w.gedcom) 
LEFT JOIN tng_events AS e ON (w.personID=e.persfamID AND w.gedcom=e.gedcom)
LEFT JOIN tng_eventtypes AS et ON (e.eventtypeID=et.eventtypeID AND 
et.tag="EVEN" AND et.description="Ehename")
LEFT JOIN tng_people AS w2 ON (f.wife=w2.personID AND f.gedcom=w2.gedcom)
LEFT JOIN tng_events AS e2 ON (w.personID=e2.persfamID AND w.gedcom=e2.gedcom)
LEFT JOIN tng_eventtypes AS et2 ON 
  (e2.eventtypeID=et2.eventtypeID AND 
  ((et2.tag IS NOT NULL AND et2.description<>"Ehename") AND !(et2.tag="EVEN" 
AND et2.description="Ehename")))

Beispiel-Ergebnisse:
http://www.ahnendaten.de/ahnen/showreport.php?reportID=225

Das geht jetzt allmählich in die richtige Richtung, aber so ganz ist es immer 
noch nicht richtig - wie unterdrücke ich nun alle Zeilen zu denjenigen 
Personen, denen ein event "Ehename" zugeordnet ist?

> da bei dem ersten join die richtigen zeilen NULL haben, setzt du eine
> condition hinten dran:
>
> WHERE
>     wExclude.personID IS NULL

Damit habe ich herumexperimentiert, aber ohne greifbares Ergebnis.

Bis hierhin jedenfalls schon mal vielen Dank!

Ulrich


***************************************
CREATE TABLE `tng_people` (
  `ID` int(11) NOT NULL auto_increment,
  `gedcom` varchar(20) NOT NULL default '',
  `personID` varchar(22) NOT NULL default '',
  `lnprefix` varchar(25) NOT NULL default '',
  `lastname` varchar(127) NOT NULL default '',
  `firstname` varchar(127) NOT NULL default '',
  `birthdate` varchar(50) NOT NULL default '',
  `birthdatetr` date NOT NULL default '0000-00-00',
  `sex` tinytext NOT NULL,
  `birthplace` text NOT NULL,
  `deathdate` varchar(50) NOT NULL default '',
  `deathdatetr` date NOT NULL default '0000-00-00',
  `deathplace` text NOT NULL,
  `altbirthdate` varchar(50) NOT NULL default '',
  `altbirthdatetr` date NOT NULL default '0000-00-00',
  `altbirthplace` text NOT NULL,
  `burialdate` varchar(50) NOT NULL default '',
  `burialdatetr` date NOT NULL default '0000-00-00',
  `burialplace` text NOT NULL,
  `baptdate` varchar(50) NOT NULL default '',
  `baptdatetr` date NOT NULL default '0000-00-00',
  `baptplace` text NOT NULL,
  `endldate` varchar(50) NOT NULL default '',
  `endldatetr` date NOT NULL default '0000-00-00',
  `endlplace` text NOT NULL,
  `changedate` datetime default NULL,
  `nickname` text NOT NULL,
  `title` tinytext NOT NULL,
  `suffix` tinytext NOT NULL,
  `nameorder` tinyint(4) NOT NULL default '0',
  `famc` varchar(22) default NULL,
  `metaphone` varchar(15) NOT NULL default '',
  `living` tinyint(4) NOT NULL default '0',
  `branch` varchar(100) NOT NULL default '',
  `changedby` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `gedpers` (`gedcom`,`personID`),
  KEY `lastname` (`lastname`,`firstname`),
  KEY `gedlast` (`gedcom`,`lastname`,`firstname`),
  KEY `gedfirst` (`gedcom`,`firstname`),
  KEY `firstname` (`firstname`),
  KEY `changedate` (`changedate`),
  KEY `local_altbirthdatetr` (`altbirthdatetr`),
  KEY `local_birthdatetr` (`birthdatetr`),
  KEY `local_burialdatetr` (`burialdatetr`),
  KEY `local_deathdatetr` (`deathdatetr`),
  KEY `local_personID` (`personID`),
  KEY `local_living` (`living`),
  KEY `local_branch` (`branch`),
  KEY `local_birthplace` (`birthplace`(255)),
  KEY `local_altbirthplace` (`altbirthplace`(255)),
  KEY `local_deathplace` (`deathplace`(255)),
  KEY `local_burialplace` (`burialplace`(255)),
  KEY `local_sex` (`sex`(1))
) TYPE=MyISAM;

CREATE TABLE `tng_families` (
  `ID` int(11) NOT NULL auto_increment,
  `gedcom` varchar(20) NOT NULL default '',
  `familyID` varchar(22) NOT NULL default '',
  `husband` varchar(22) NOT NULL default '',
  `wife` varchar(22) NOT NULL default '',
  `marrdate` varchar(50) NOT NULL default '',
  `marrdatetr` date NOT NULL default '0000-00-00',
  `marrplace` text NOT NULL,
  `marrtype` varchar(50) NOT NULL default '',
  `divdate` varchar(50) NOT NULL default '',
  `divdatetr` date NOT NULL default '0000-00-00',
  `divplace` text NOT NULL,
  `status` varchar(20) NOT NULL default '',
  `sealdate` varchar(50) NOT NULL default '',
  `sealdatetr` date NOT NULL default '0000-00-00',
  `sealplace` text NOT NULL,
  `husborder` tinyint(4) NOT NULL default '0',
  `wifeorder` tinyint(4) NOT NULL default '0',
  `changedate` datetime default NULL,
  `living` tinyint(4) NOT NULL default '0',
  `branch` varchar(100) NOT NULL default '',
  `changedby` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `familyID` (`gedcom`,`familyID`),
  KEY `wife` (`gedcom`,`wife`),
  KEY `changedate` (`changedate`),
  KEY `husband` (`gedcom`,`husband`),
  KEY `local_living` (`living`),
  KEY `local_marrdatetr` (`marrdatetr`),
  KEY `local_wife` (`wife`),
  KEY `local_husband` (`husband`),
  KEY `local_marrplace` (`marrplace`(255))
) TYPE=MyISAM;

Die Tabelle tng_families ist über die Felder "husband" und "wife" mit dem Feld 
"personID" von tng_people verknüpft.

 CREATE TABLE `tng_events` (
  `eventID` int(11) NOT NULL auto_increment,
  `gedcom` varchar(20) NOT NULL default '',
  `persfamID` varchar(22) NOT NULL default '',
  `eventtypeID` int(11) NOT NULL default '0',
  `eventdate` varchar(50) NOT NULL default '',
  `eventdatetr` date NOT NULL default '0000-00-00',
  `eventplace` text NOT NULL,
  `age` varchar(12) NOT NULL default '',
  `agency` varchar(120) NOT NULL default '',
  `cause` varchar(90) NOT NULL default '',
  `addressID` varchar(10) NOT NULL default '',
  `parenttag` varchar(10) NOT NULL default '',
  `info` text NOT NULL,
  PRIMARY KEY  (`eventID`),
  KEY `persfamID` (`gedcom`,`persfamID`),
  KEY `local_eventtypeID` (`eventtypeID`),
  KEY `local_eventdatetr` (`eventdatetr`),
  KEY `local_addressID` (`addressID`),
  KEY `local_parenttag` (`parenttag`),
  KEY `local_eventplace` (`eventplace`(255))
) TYPE=MyISAM;

Events sind so etwas wie flexible Zusatzdaten, die man zu einer Person (oder 
Familie) in beliebiger Art und Menge hinzufügen kann. D.h. einem Datensatz 
aus tng_peoples können keine, eine oder mehrere DS aus tng_events zugeordnet 
sein.
Ein Datensatz aus der Tabelle tng_events ist über das Feld persfamID mit dem 
Feld personID aus tng_people verknüpft.

CREATE TABLE `tng_eventtypes` (
  `eventtypeID` int(11) NOT NULL auto_increment,
  `tag` varchar(10) NOT NULL default '',
  `description` varchar(90) NOT NULL default '',
  `display` text NOT NULL,
  `keep` tinyint(4) NOT NULL default '0',
  `ordernum` smallint(6) NOT NULL default '0',
  `type` char(1) NOT NULL default '',
  PRIMARY KEY  (`eventtypeID`),
  UNIQUE KEY `typetagdesc` (`type`,`tag`,`description`),
  KEY `ordernum` (`ordernum`),
  KEY `local_tag` (`tag`),
  KEY `local_tag_descr` (`tag`,`description`)
) TYPE=MyISAM;

Die Tabelle tng_eventtypes ist über das Feld "tag" mit dem Feld "eventtypeID" 
aus der Tabelle tng_events verknüpft.

Das Feld "gedcom" in den Tabellen ist sowas wie ein Mandant - vgl. die UNIQUE 
KEYs.

_______________________________________________
Allgemeine Infos zur Liste: http://www.4t2.com/mysql/
Verwaltung: https://lists.4t2.com/cgi-bin/mailman/listinfo/mysql-de