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

[Friedward74]

Sorry, aber was SQL betrifft bin ich halt noch ein Newbie und desweiteren handelt es sich hier um mein erstes JOIN-Statement. Allerdings gibt es in dem ON-Feld sehr wohl ein = bzw. ein <= Allerdings hoff ich vor einem neuen Versuch, doch auf ein paar Anregungen.

[thommyk]

1. Es ist Nur EIN FROM-statement erlaubt, alle anderen Tabelle mit JOIN verbinden
2. Es ist nur EIN WHERE-statement erlaubt

Abgesehen davon dass da jetzt mehrere Versionen der Distanzberechnung drin sind, nehme ich jetzt einfach mal die aus der ersten Abfrage an

Dann müsste es gehen mit

SELECT user_tbl.*,geo2.plz, (6367.41*SQRT(2*(1-cos(RADIANS(geo1.breite))*cos(geo1.breite)*
(sin(RADIANS(geo1.laenge))*sin(geo1.breite)+cos(RADIANS(geo1.laenge))*
cos(geo1.laenge))-sin(RADIANS(geo1.breite))*sin(geo1.breite)))) 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(geo2.breite))*cos(geo1.breite)*
(sin(RADIANS(geo2.laenge))*sin(geo1.breite)+cos(RADIANS(geo2.laenge))*
cos(geo1.laenge))-sin(RADIANS(geo2.breite))*sin(geo1.breite)))) <=".$umkreis."
WHERE alter>'18' AND geschlecht='m'
ORDER BY Distance 

Allerdings fehlt in Deiner WHERE-Klausel noch die Ausgangs-PLZ.
also noch AND geo1.plz = '$plz' dranhängen in die WHERE-Klausel

Wenn Du Feldnamen in JOINS verwendest, immer die Tabellennamen davor, sonst gibts Probleme bei gleichnamigen Feldern in verschiedenen Tabellen

P.S. Hab mich vertan. Subqueries gehen ab Mysql 4.1. Dürfte aber noch dauern, bis die bei den meisten Providern angekommen ist.

[Friedward74]

Die Abfrage läuft 1 A durch. Die Syntax stimmt also. Allerdings werden immer nur die Datensätze mit der PLZ ausgegeben aus dem letzten WHERE-Statement ausgegeben. geo2.plz wird ebenso wie die distance auch nicht ausgegeben
Woran kann das liegen?

Hier nochmal die verwendete Abfrage:

SELECT user . * , geo2.plz, ( 6367.41 * SQRT( 2 * ( 1 - cos( 
RADIANS( geo2.breite ) ) * cos( geo1.breite ) * ( sin( RADIANS( 
geo2.laenge ) ) * sin( geo1.breite ) + cos( RADIANS( geo2.laenge ) )
 * cos( geo1.laenge ) ) - sin( RADIANS( geo2.breite ) ) * sin( 
geo1.breite ) ) ) ) AS `distance`
FROM user
LEFT JOIN geodb AS geo1
USING ( plz )
LEFT JOIN geodb AS geo2 ON ( 6367.41 * SQRT( 2 * ( 1 - cos( 
RADIANS( geo2.breite ) ) * cos( geo1.breite ) * ( sin( RADIANS( 
geo2.laenge ) ) * sin( geo1.breite ) + cos( RADIANS( geo2.laenge ) ) 
* cos( geo1.laenge ) ) - sin( RADIANS( geo2.breite ) ) * sin( 
geo1.breite ) ) ) ) <=100
WHERE geo1.plz = '48153'
AND ************ = 'm' AND age >'18'
ORDER BY distance 

[thommyk]

Denkfehler meinerseits.
Die Verknüpfung der user und geo1 mit USING(plz) war falsch.
Zweiter Versuch:

SELECT user . * , geo2.plz, ( 6367.41 * SQRT( 2 * ( 1 - cos( 
RADIANS( geo2.breite ) ) * cos( geo1.breite ) * ( sin( RADIANS( 
geo2.laenge ) ) * sin( geo1.breite ) + cos( RADIANS( geo2.laenge ) )
 * cos( geo1.laenge ) ) - sin( RADIANS( geo2.breite ) ) * sin( 
geo1.breite ) ) ) ) AS `distance`
FROM user
LEFT JOIN geodb AS geo1
ON (geo1.plz = '48153')
LEFT JOIN geodb AS geo2 ON ( 6367.41 * SQRT( 2 * ( 1 - cos( 
RADIANS( geo2.breite ) ) * cos( geo1.breite ) * ( sin( RADIANS( 
geo2.laenge ) ) * sin( geo1.breite ) + cos( RADIANS( geo2.laenge ) ) 
* cos( geo1.laenge ) ) - sin( RADIANS( geo2.breite ) ) * sin( 
geo1.breite ) ) ) ) <=100 AND user.plz=geo2.plz
WHERE ************ = 'm' AND age >'18'
ORDER BY distance 

Erklärung: Die user-Tabelle wird mit der geo1 über die Ausgangsplz verknüpft, aber ohne die Zeilen in der PLZ zu beschränken wie vorher (durch WHERE geo1='48153')
Dann wird auf die geo2 verknüpft mit der Umkreisbeschränkung UND
übereinstimmender PLZ mit der user-Tabelle
Am Schluss noch die weiteren WHERE-Klauseln

Warum die Distance nicht angezeigt wird, weiss ich nicht. Das ist Deine Formel, die prüfst Du bitte selbst.
Wenn die Ergebnisse nicht stimmen, mach mal ein SELECT * FROM ....
um alle Spalten zu erhalten und ggf. zu kontrollieren

[Friedward74]

Danke nochma. Hab´s inzwischen (ne Weile hat ich anderes um die Ohren) hingefrickelt. Einen kleinen Fehler hattest Du allerdings noch drin: user.plz=geo2.plz gehört in die WHERE-Clause.

Was mir allerdings Sorgen bereitet ist die Performance der ORDER BY Sortierung. Die Berechnung der Distanz (auch beim zweiten Mal) kostet ja so gut wie keine Zeit (insgesamt bei 4000 Datensätzen 0,6 Sekunden). Die Sortierung hingegen bringt die Abfragedauer schon auf über 3 Sekunden hoch. Das ist schon verdammt lang. Vor allem wenn man bedenkt, dass die Query ja noch diverse weitere Kriterien hat. Das war übrigens die Performance auf dem lokalen System. Auf dem Server mit weiteren Userzugriffen sieht´s noch wesentlich dramatischer aus.

Gibt´s da irgendwie ne Möglichkeit evtl. die Sortierung schon im JOIN vorzunehmen oder ne andere elegantere und vor allem performantere Lösung? Oder wäre ein Subselect (wenn ich denn auf MySQL 4.1 Zugriff hätte) doch bessere Performance bringen?

[thommyk]

ORDER BY bei grossen Tabellen ist meist performance-lastig
Aber 3 Sekunden finde ich noch passabel.

Gibt allerdings keine andere Möglichkeit mit SQL.
"vorsortieren" in der Tabelle kannst Du ja nicht, weil die Distance jedesmal neu berechnet wird.

Ich würde höchstens mal probieren, die Sortierung mit PHP zu machenmit sort() und gucken obs schneller ist.

[Friedward74]

Hab´s gerad mal auf dem Server probiert. Auch ohne Order By bricht er dort (knapp 8000 Datensätze) ab. Wahrscheinlich wird da die Prozesszeit sogar überschritten.

Also hat die ganze Grübelei und Probiererei somit wohl noch nicht viel gebracht. Allerdings kann ich mir kaum vorstellen, dass es mit dem Umweg über php schneller geht. Ich hab mal gelernt, dass es grundsätzlich immer schlauer ist die Arbeit von der DB erledigen zu lassen.

Allerdings ist mir noch ne andere Idee gekommen. Vielleicht sollte ich einfach Längen und Breitengrade bei den Usern anspielen. Damit spar ich mir schon die JOINS kann die Entfernung direkt selektieren und muß nicht den Umweg über die PLZ gehen. Könnte das klappen?

Wie funktioniert eigentlich so eine Datenanspielung aus ner anderen Tabelle? Vielleicht über ein UPDATE mit JOIN über die PLZ?.

[thommyk]

Was meinst Du mit "Datenanspielung"?
Doch eher Datenübertragung von einer Tabelle in die andere.

Entweder Machst Du ein
REPLACE INTO Tabelle1
SELECT Tabelle1.Feld1, Tabelle2.Feld2, Tabelle2.laenge, Tabelle2.breite, ...
FROM Tabelle1
LEFT JOIN Tabelle2 ON () ....

wobei Du die Reihenfolge der Spalten in Tabelle 1 genau einhalten musst und dann jeweils die Felder aus der richtigen Tabelle nimmst.

Oder Du machst ein
UPDATE Tabelle1 LEFT JOIN Tabelle2 ON() bzw. USING ()
SET Tabelle1.laenge = Tabelle2.laenge, Tabelle1.breite = Tabelle2.breite
WHERE ....
dürfte einfacher sein

[Friedward74]

Danke, hab auch schon recherchiert aber laut:


http://www.php-resource.de/forum/sho...threadid=33604

sind JOINS in Updates erst ab MySQL 4 möglich.

Vielleicht wart ich mit der ganzen Funktionalität ja doch noch, bis mein Provider auf ne 4er Version umgestellt hat. Da scheint ja doch einiges einfacher zu werden.

[Friedward74]

...und nu das umgekehrte Problem

...das obige Problem mit der Umkreissuche hab ich inzwischen ja nach diversen Performancetests gelöst (Ohne Join, aber mit Array). Also erst alle PLZ ermitteln die in einem bestimmten Umkreis liegen und dann alle Datensätze mit den entsprechenden PLZ anzeigen lassen.

Jetzt steh ich allerdings vor einem noch schwierigeren Problem.

Problemstellung:
zwei Tabellen:
geo_db (mit PLZ und Koordinaten zur Entfernungsberechnung)
user_db (mit Userdaten u.a. gewünschte Entfernung und PLZ, des Wunschpartners)

Ich möchte gerne die User selektieren, in deren Wunschentfernungsumkreis eine bestimmte PLZ (die des Profils vom Anfragenden) der Anfragende liegt.

Hat jemand ne Idee wie man´s machen könnte?