[SQL allgemein] Join über 2 Tabellen mit doppelter WHERE-Abfrage

[Friedward74]

Hallo zusammen,

ich bin gerade am verzweifeln, da ich bei meiner ersten JOIN-Abfrage gleich eine riesige Abfrage bewältigen muß. Für Euch vielleicht kein Problem. Hier also kurz die Erklärung:

Ich hab zwei Tabellen:
user (mit allen Mitgliedsdaten)
geodb (mit koordinaten, plz und so fort)

Bisher frage ich alle Mitglieder aus der user-Tabelle bereits über eine größere WHERE-Abfrage mit diversen Kriterien ab:
(sehr stark vereinfacht)

SELECT * FROM user WHERE alter>'18', geschlecht='m' ORDER BY lastlogin 

Weiter möchte ich mir alle Mitglieder ausgeben lassen, welche innerhalb eines bestimmten Umkreises wohnen. Nun kommt die zweite Tabelle ins Spiel. Hier ermittle ich mit unten stehender Abfrage aus Posting http://www.php-resource.de/forum/showthread/t-5180.html
alle PLZ mit Angabe der Entfernung, welche sich innerhalb einer bestimmten Distanz zur Ausgangs-PLZ befinden:

SELECT ort, plz,(6367.41*SQRT(2*(1-cos(RADIANS(breite))*cos(".$breite.")*
(sin(RADIANS(laenge))*sin(".$laenge.")+cos(RADIANS(laenge))*
cos(".$laenge."))-sin(RADIANS(breite))*sin(".$breite.")))) AS
Distance FROM geodb_locationsWHERE 6367.41*SQRT(2*
(1-cos(RADIANS(breite))*cos(".$breite.")*(sin(RADIANS(laenge))*
sin(".$laenge.")+cos(RADIANS(laenge))*cos(".$laenge."))-
sin(RADIANS(breite))*sin(".$breite."))) <=".$umkreis." 
ORDER BY Distance 

Was jetzt kommt, kann man sich ja schon fast denken. Über nen JOIN möchte ich jetzt diese ermittelten PLZen als zusätzliches Selektionskriterium für die User verwenden. Weiter soll die hier ermittelte Distance auch bei jedem User mit ausgegeben werden. Weiter möchte ich alternativ beide ORDER BY-Kriterien (DISTANCE und lastlogin), optimalerweise primär nach Distanz und sekundär nach lastlogin anwenden.

Hat einer von Euch Cracks hier vielleicht ne Idee oder gar ne Lösung auf Lager?

Viele Grüße,

Friedward

[TobiaZ]

Bitte brich deinen Beitrag gemäß den Forenregeln (http://www.php-resource.de/forum/sho...threadid=47906) um!

[RanzigeMilch]

wird das ne partnersuche?

[prego]

Hi,

http://www.opengeodb.de

ich geh mal auf, von denen hast du auch die geoDB. Die haben auf jeden Fall bei den Exmaples das schon implementiert - vielleicht kannste dir da ja was abgucken.

[TobiaZ]

ich geh mal auf,

OffTopic:

---

Viel Spaß da oben!

---

[Friedward74]

Immer noch keine Lösung

Hi TobiaZ,

bin noch neu hier. Gegen welche Regel genau soll ich denn verstoßen haben?

Hi RanzigeMilch,
jepp, ne Partnersuche. Die läuft auch schon (bis auf die Entfernungsgeschichte) auf http://www.MeetMeOnline.de

Hi prego,
bei opengeodb hab ich auch schon gesucht, bin aber nicht fündig geworden. Wenn Du n Beispiel gefunden hast, wär ich dankbar für nen Link. Eigentlich geht es mir ja auch nur um die Systematik dieses JOINS.

[TobiaZ]

Hmm, dabei hatte ich schon ausnahmsweise dazu geschrieben, dass du bitte deinen Code umbrechen sollst.

Wenn du die Regeln gelesen hast, ist dir sicher der Absatz über horizontale Scrollbalken aufgefallen..

[prego]

OffTopic:

---

lol, meinte natürlich da solltest du hin gehen... muhaha

---

Ist überhaupt für deinen User auch irgendwo der/die Ort/PLZ eingetragen, weil du musst ja etwas haben, was in beiden Tabellen gleichermaßen vorhanden ist...

[Friedward74]

sorry, nu hab ich´s gerafft.

[Friedward74]

...die PLZ hab ich natürlich in beiden Tabellen verfügbar. Ansonsten macht das ganze ja auch keinen Sinn.

[thommyk]

SELECT ort, plz,(6367.41*SQRT(2*(1-cos(RADIANS(breite))*cos(".$breite.")*
(sin(RADIANS(laenge))*sin(".$laenge.")+cos(RADIANS(laenge))*
cos(".$laenge."))-sin(RADIANS(breite))*sin(".$breite.")))) AS
Distance FROM geodb_locationsWHERE 6367.41*SQRT(2*
(1-cos(RADIANS(breite))*cos(".$breite.")*(sin(RADIANS(laenge))*
sin(".$laenge.")+cos(RADIANS(laenge))*cos(".$laenge."))-
sin(RADIANS(breite))*sin(".$breite."))) <=".$umkreis."
ORDER BY Distance

Wo ist denn da die Ausgangs-PLZ?? Oder bin ich blind?
Bzw. wo kommen $breite und $laenge her. Sind das die Koordinaten der
Ausgangsplz?

[Friedward74]

Genau, $breite und $laenge sind die Kordinaten der Ausgangsplz, welche ebenfalls aus der Tabelle geodb kommen. Die Abfrage erfolgt vorher und ist hier nicht aufgezeigt (kann aber natürlich auch integriert werden).

Ergebnis dieser Abfrage sollen nur die diversen den Umkreis fallenden PLZ sein (Ort stammt aus der Abfrage in dem anderen Posting ist aber eigentlich überflüssig). Meine Frage ist immer noch, wie verknüpfe ich das Ergebnis dieser Abfrage (nicht über ein Array, sondern) per JOIN mit der anderen Tabelle, in denen meine Mitgliedsdaten mit PLZ gespeichert sind.

[thommyk]

Lösung1:
Du speicherst die ermittelten PLZ in einem Array z.B. $matchingZips.
Nehme mal an, dass die PLZ als VARCHAR oder CHAR gespeichert sind.

Du machst einen JOIN auf die Tabelle mit den PLZ
mit

LEFT JOIN geodb_locations
ON (geodb_locations.plz IN ('".implode("','", $matchingZips)."') 

Wenn Du die Entfernung auch noch mit ausgeben lassen willst, musst Du sie nochmal in das SELECT ... FROM einbauen

Lösung2:
Wenn Du die beiden Abfragen zu einer zusammenfassen willst wirds ein bisschen komplizierter.
Im Prinzip müsstest Du sowas machen

SELECT * FROM user WHERE alter>'18' AND geschlecht='m' AND
plz IN (SELECT ... FROM geodb_locations...) 

Das geht aber erst ab Mysql 5.0

Lösung3:
Also musst Du im Prinzip deine Abfragen zur Längen und Breitenermittlung und die Umkreissuche zusammenbasteln.

SELECT ...
FROM erste Tabelle User
LEFT JOIN zweite Tabelle geodb_locations AS `geo1`
LEFT JOIN dritte tabelle geodb_locations AS `geo2`
WHERE

Die benötigten Felder der PLZ-Abfrage packst Du mit den richtigen Tabellennamen auch ins SELECT user.*, geo2.plz, geo2.ort, ... AS `Distance`

von der Tabelle user ein JOIN mit USING(plz) auf geo1.
Daraus nimmst Du die Werte für Länge und Breite des Ortes für die Distanzberechnung und machst dann einen JOIN auf geo2
mit ON (berechnete Distance < $umkreis)

Bei 'berechnete Distance' musst Du dann leider nochmal die ganze Formel eintragen wie beim SELECT vorher. (wie in deiner Originalabfrage)
Dann kommt noch Deine WHERE Klausel

Dann hast Du schon fast alles, was Du wolltest.
Mit dem ORDER BY kommst Du ja wohl alleine klar.

[Friedward74]

Danke erstmal für die Mühe.

Zu Lösung 1 :
Also bis zur Ausgabe der PLZ mit Entfernungen war ich ja schon. Die Abfrage sollte dann eigentlich auf meine User-Tabelle gehen, oder? Hier greifst Du aber auch auf das Array zurück. Das wollte ich aus Performancegründen ja eigentlich umgehen. Ausserdem fehlt mir auch noch die Info, wie ich die unterschiedlichen Entfernungen der einzelnen Postleitzahlen zur Ursprungspostleitzahl bei den gefundenen Usern der jeweiligen Postleizahl mit ausgebe/selektiere. Ansonsten funktioniert der Order by ja nach Entfernung ja auch gar nicht.

Zu Lösung 2 :
Die Variante mit dem Subselect hatt ich auch schon durchdacht. Wäre auch ganz sinnig. Funktioniert übrigens schon ab 4.2. Mein Provider stellt allerdings nur ne 3.X Version zur Verfügung Daher dachte ich, auch da die Abfrage über zwei Tabellen geht, dass man das irgendwie über nen JOIN regeln könnte. Aber ist wohl doch nicht so einfach.

Zu Lösung 3 :
Das sieht schon besser aus und geht auch in die Richtung wie ich mir das vorgestellt habe. Allerdings weiß ich nicht, ob ich die Abfrage so funktionsfähig zusammenkriege.

$latitudeRad = deg2rad($plz_item1->breite);
$longitudeRad = deg2rad($plz_item1->laenge);
$formula = "COALESCE(IFNULL((ACOS((SIN($latitudeRad)*
SIN(RADIANS(breite))) + (COS($latitudeRad)*
COS(RADIANS(breite))*COS(RADIANS(laenge)-$longitudeRad))) * 
$plz_erdradius),0))";

SELECT user_tbl.*,geo2.plz, ".$formula." AS `Distance` 
FROM user_tbl
LEFT JOIN geodb AS geo1 USING(plz)
LEFT JOIN geodb AS geo2 ON 
(6367.41*SQRT(2*(1-cos(RADIANS(breite))*cos(".$breite.")*
(sin(RADIANS(laenge))*sin(".$laenge.")+cos(RADIANS(laenge))*
cos(".$laenge."))-sin(RADIANS(breite))*sin(".$breite.")))) AS
Distance FROM geodb_locationsWHERE 6367.41*SQRT(2*
(1-cos(RADIANS(breite))*cos(".$breite.")*(sin(RADIANS(laenge))*
sin(".$laenge.")+cos(RADIANS(laenge))*cos(".$laenge."))-
sin(RADIANS(breite))*sin(".$breite."))) <=".$umkreis."
WHERE alter>'18' AND geschlecht='m'
ORDER BY Distance 

Mal abgesehen, dass das noch nicht hinhaut, ergibt das doch ne grausige Performance. Nicht nur, dass man einmal die Entfernung für die Postleitzahlen in dem Radius berechnet. Hier muß doch für jeden Treffer (das können bei mir locker mal 1000 sein) jedesmal die Entfernung berechnet werden. Werden hier überhaupt in der Tabelle user_tbl mehrere PLZ abgefragt? Irgendwie hab ich´s glaub ich immer noch nicht verstanden...

[xManUx]

Mal abgesehen davon, dass ich zweimal FROM lesen, zweimal WHERE, dass ON ohne = o.ä. steht, wage ich schwer zu bezweifeln, dass das überhaupt was nutzt. Und müsste dir sämtliche Fehlercodes zuflüsstern?!