Datenbankabfrage

[cell]

Hallo,

Ich habe eine Usertabelle "Users": ID (Int), Name (Varchar), ... (und noch ein paar Eigenschaften). In einer weiteren Tabelle "Sprachen": ID (Int), user_id, (Int) Sprache (Varchar), Anzahl_Jahre (decimal) stehen die Sprachen der user und wie lange sie diese gelernt haben.

Nun möchte ich in einem Select zB alle ausgeben die Englisch mehr als 4 Jahre und Russisch weniger als 3 Jahre sprechen.

Meine erste Idee ist:

Select ID, Name from Users, Sprachen where

Users.id = Sprachen.user_id and

Users.id in (Select Sprachen.user_id from Sprachen where Sprache = Englisch and Anzahl_Jahre > 4) and

Users.id in (Select Sprachen.user_id from Sprachen where Sprache = Russisch and Anzahl_Jahre < a)

mich stören die zusätzlichen selects in der where-klausel. kann man das irgendwie besser schreiben/verknüpfen ohne die ganzen ids erst mit den selects in arrays zu packen?

[Fraggla]

ich selbst bin zwar im Abfragenschreiben noch nicht so fit, aber wie wärs mit nem

Join?

[Stefan87]

Hi,

ich bin mir nicht ganz sicher, aber probier den SELECT mal aus.

Der müsste normal alle ermitteln die entweder English mehr als 4 Jahre sprechen oder Russich weniger als 3 Jahre und aus der Menge der Personen ermittelt er dann die, die 2 mal in der Liste vorkommen also beide Eigenschaften haben.

SELECT ID, NAME

FROM USERS

INNER JOIN SPRACHEN ON USERS.ID = SPRACHEN.USER_ID

WHERE (SPRACHEN = ENGLISCH AND ANZAHL_JAHRE > 4) OR (SPRACHEN = RUSSISCH AND ANZAHL_JAHRE < 3)

GROUP BY ID, NAME HAVING COUNT(*) > 2;

[FISI from Hell]

Du wirst um die Subselects nicht herumkommen, denn du musst ja die Jahre mit den Sprachen in Verbindung bringen die ID's kann man weglassen, da du ja schon eine Verknüpfung der ID's hast.

Select Users.ID, Name from Users, Sprachen where --Die Datenbank weiß sonst nicht welche ID gemeint ist.

Users.id = Sprachen.user_id and

(Select Anzahl_Jahre from Sprachen where Sprache = Englisch) >4 and

(Select Anzahl_Jahre from Sprachen where Sprache = Russisch) <3

@Stefan ok überzeugt geht auch ohne Subselect. Dennoch weiß die Datenbank immer noch nicht welche ID gemeint ist. Und man braucht eigentlich auch nur nach ID gruppieren.

Ich glaube nämlich nicht das 1 user 2 ID besitzt und ich hoffe zu dem Namen existiert noch eine Spalte vorname. Sonst gibt es in der Prüfung ärger.

Bearbeitet 31. März 2011 von FISI from Hell

[cell]

Danke für eure Hilfe.

SELECT ID, NAME

FROM USERS

INNER JOIN SPRACHEN ON USERS.ID = SPRACHEN.USER_ID

WHERE (SPRACHEN = ENGLISCH AND ANZAHL_JAHRE > 4) OR (SPRACHEN = RUSSISCH AND ANZAHL_JAHRE < 3)

GROUP BY ID, NAME HAVING COUNT(*) > 2;

Das klappt, wobei COUNT(*) > 2 aber >1 oder >=2 sein muss. Problem ist nur dass durch das Gruppieren die Ausführungszeit sehr lange ist. Mein select brauch ca 0,7 SEkunden im PHPMyadmin und deiner 1,2 Sekunden (ohne Gruppieren brauch er auchg 0,7 Sekunden). Dafür braucht es vermutlich aber weniger Speicher.

Gibts noch ne möglichkeit wo man nicht Gruppieren muss? Vielleicht irgendwie mit Variablen (MySQL :: MySQL 5.0 Reference Manual :: 8.4 User-Defined Variables).

@ FISI from Hell

Danke für den Hinweis. Die tabellen beinhalten noch ein paar mehr spalten und der gesammte SQL-Befehl ist noch etwas größer. Had den Select-befehl mal schnell zusammengeschustert und dabei das für dieses Problem nicht relevante weggelassen.

[cell]

Ich habe den Select noch etwas folgt abgeändert. Dadurch macht er den Select Abfragen nur bei denen "Sprache = Russisch and Anzahl_Jahre < 3" zutrifft. Schneller wird die Abfrage dadurch aber auch irgendwie nicht (Immernoch bei ca 0,7 Sekunden).

Select Users.id, Name from Users, Sprachen where

id = Sprachen.user_id and

id in (Select Sprachen.user_id from Sprachen where Sprachen.id in (Select id from Sprachen where Sprache = Russisch and Anzahl_Jahre < 3) and Sprache = Englisch and Anzahl_Jahre > 4)

[Stefan87]

Ja ist halt auch kein Wunschkonzert hier. ;-)

Ist das denn nen Problem wenn du anstatt 0,7 1,2 Sekunden brauchst ?

Bzw. warum willst du keine SubSelects nutzen ?

[cell]

Also bei den meisten Seiten ist es mehr oder weniger egal ob nen SQL-Befehl 0,7 oder 1,2 Sekunden brauch.

Aber bei Suchfunktionen oder anderen Funktionen die zeit-/ performancekrittisch werden könnten ist es mir schon wichtig dass die Skripte und SQL-Anweisungen schnell sind und nicht unnötig RAM brauchen. Da kommt performance gleich nach Sicherheit und da kann der Code meinetwegen auch häßlich aussehen solang es möglichst schnell geht^^

Ich nehm schon subselects, solange es keine bessere Möglichkeit gibt.

lg Cell

[FISI from Hell]

Erstens bezweifle ich das ein Subselect im Subselect bei größeren Datenmenge auch noch so stabil läuft und zweitens

Select Users.id, Name from Users, Sprachen where

id = Sprachen.user_id and

id in (Select Sprachen.user_id from Sprachen where Sprachen.id in (Select id from Sprachen where Sprache = Russisch and Anzahl_Jahre < 3) and Sprache = Englisch and Anzahl_Jahre > 4)

Welche?

Sehr umständlich. Da wird Stefans und mein Vorschlag schon eleganter Aussehen.

Unnötig, da du ja nur eine Tabelle hast in diesem Subselect.