mySQL Abfrage mit WHERE über mehrer Zeilen(!)

[MadCro]

Hallo zusammen,

komme nicht weiter und brauche bitte Untertützung.

Hab eine Tabelle in welcher mehrere Eigenschaften stehen,

einere weitere Tabelle in welcher Personen stehen und eine dritte in welcher die Zuordnung Person zur Eigeschaft hinterlegt ist, dabei gilt jede person kann n eigeschaften haben und dabei auch eine "Benotung" (wie gut sie/er diese Eigenschaft kann) dieser Eigeschaft.

D.h. z.B.

Person:

persoID: 1 | persoName: M. Mustermann

persoID: 2 | persoName: F. Musterfrau

Eigenschaft:

SkillID: 1 | SkillKommentar: Finanzen

SkillID: 2 | SkillKommentar: Technik

SkillID: 3 | SkillKommentar: Sprachen

und entsprechend die Cross Tabelle

persoID: 1 SkillID:2 rating:100%

persoID: 1 SkillID:3 rating:80%

persoID: 2 SkillID:3 rating:80%

persoID: 2 SkillID:1 rating:20%

usw.

Anzahl Personen und Eigeschaften ist "n"

Jetzt brauche ich ein schlaues Query mit welchem ich alle Personen bekomme die

Eigeschaft1 und Eingeschaft2 haben und zwar sortiert nach dem "rating"

Hab das nun über JOIN der Tabellen auf sich selbst die ganze Zeit versucht komme aber nicht weiter, zweiter Ansatz war über temporty tabellen - das hat mich aber auch nicht zum Ziel gebracht. Ein "WHERE SkillID IN (Skill1 AND Skill2)" funkzt auch nicht

Bin für jeden Ansatz dankbar !

VG

[CSharp92]

Select * from xxxxx WHERE persoID = '1' AND SkillID = '2';

haben und zwar sortiert nach dem "rating"

Ah, okay.

Das würde ich mit PHP über sort(); machen.

Hmm?

Nochmal Edit: Natürlich noch den rating im Query entfernen

Bearbeitet 4. Oktober 2011 von CSharp92

[MadCro]

Hi CSharp92,

danke für Deine Antwort.

Nochmal die Anforderung

ich kenne die ID der Person nicht, genau die will ich ja herausfinden und zwar über die "Skills"

hab inzwischen rausgefunden das es über ein self join gehen soll, bekomme es aber immer noch nicht so hin wie ich es brauche

select * FROM tblxuser2skill t1, tblxuser2skill t2 , tbluser

WHERE t1.A2Q_A_ID=A_ID AND (t1.A2Q_AQCS_ID=1 AND t2.A2Q_AQCS_ID=2)

ORDER BY t1.A2Q_Rating DESC, t2.A2Q_Rating DESC

In tblxuser2skill steht die Zurdnung Person zur Skill, in tbluser die Personen

A2Q_A_ID ist die ID der Peson in der Tabelle tblxuser2skill genau so wie die A_ID

A2Q_AQCS_ID ist die ID der Eigenschaft/Skill

Ich bekomme mit der Abfrage mehrfach die selbe Zeile zurück, hmm

EDIT:

ok hab gerade gelernt das SELF JOINS nur Sinn bei Hierarchien in einer Tabelle machen, was bei mir nicht der fall ist, also wieder bei 0

Bearbeitet 4. Oktober 2011 von MadCro

[Jejerod]

Person:

persoID: 1 | persoName: M. Mustermann

persoID: 2 | persoName: F. Musterfrau

Eigenschaft:

SkillID: 1 | SkillKommentar: Finanzen

SkillID: 2 | SkillKommentar: Technik

SkillID: 3 | SkillKommentar: Sprachen

und entsprechend die Cross Tabelle

persoID: 1 SkillID:2 rating:100%

persoID: 1 SkillID:3 rating:80%

persoID: 2 SkillID:3 rating:80%

persoID: 2 SkillID:1 rating:20%

Unverifiziert, aber vielleicht ein Ansatz:

SELECT persoID, matches, total FROM (

        SELECT persoID, COUNT(persoID) AS matches, SUM(rating) AS total

        FROM crosstable WHERE SkillID IN (1,3)

        GROUP BY persoID ) AS groupview

WHERE matches=2 ORDER BY total DESC

davon ausgehend das rating eine aufsummierbare Zahl ist, und das der Subselect als matches die Anzahl der Treffer der WHERE Bedingung (SkillID IN (...)) rausrückt.

[MadCro]

Hallo Jejerod ,

danke für Deine Antwort

Leider geht ein Group by Rating nicht, da die Skills prios haben, hatte ich vergessen dazu zu sagen - sorry mein Fehler !

ich habe aber Deinen Ansatz mit dem SubSelect aufgenommen, muss aber feststellen das man anscheinden bei den SubSelects keinen ORDER MY machen kann, bzw. das scheint mySQL egal zu sein, Ansatz war:

SELECT * FROM crosstable  WHERE SkillID = 1 AND persoID IN (SELECT persoID FROM crosstable  WHERE SkillID = 3 ORDER BY rating desc) ORDER BY rating desc

 

dies sollte nun für den Fall gehen das zwei Skills in Frage kommen, wenn es mehr als zwei werden sollen kann man dann noch ein SubSelect erweitern - so zumindest mein Ansatz. Scheint mir aber etwas "dreckig" zu sein . Wenn ich das so mache wie oben, "scheint es" erstmal zu klappe, d.h. ich bekomme eine ergebniss menge in der saubren Reihenfolge (abhänging von den Ratings) - kann aber auch zufall sein - da bin ich mir eben nicht sicher ob das ORDER BY im SubSelect funzt wenn ich dann aber noch eine dritte Tabelle, und zwar die Personen mit LEFT JOIN einbinden und noch aus dieser Tabelle zwei Kriterien im WHERE Teil habe, dann zer*******t es mir wieder die Reihenfolge.

SELECT * FROM crosstable LEFT JOIN personal  WHERE PersoKriterium1='Y' AND PersoKriterium2=5 AND

SkillID = 1 AND persoID IN (SELECT persoID FROM crosstable  WHERE SkillID = 3 ORDER BY rating desc) ORDER BY rating desc

[Termi]

Hey,

in deinem letzten Statement steht

SELECT * FROM crosstable LEFT JOIN personal  WHERE PersoKriterium1='Y' AND PersoKriterium2=5 AND

[b]SkillID = 1[/b] AND persoID IN (SELECT persoID FROM crosstable  WHERE [b]SkillID = 3[/b] ORDER BY rating desc) ORDER BY rating desc

seh ich richtig das du sagst das du alle leute mit der SkillId 1 haben willst, suchst dann aber die persoID von den leuten raus die SkillID 3 haben? Oder irre ich mich grad? Vielleicht liegt da ja der Fehler.

Ansonsten:

Was sind denn PersoKriterium und PersoKriterium2? Die hattest du oben garnicht erwähnt

Lg

[MadCro]

Hi Termi,

ich will alle Personen haben die sowohl Skill 1 als auch Skill 3 haben, aber so geordnet das mir zuerst die Personen mit dem höchsten Rating für Skill 1 und dann mit dem höchsten Rating für Skill 3 zurück gegeben werden.

PersoKriterium1 und PersoKriterium2 sind "einfach nur Felder", die spielen ja beim ORDER BY keine Rolle. ich bekomme schon die richtigen Personen (also solche die zu dem WHERE matchen) zurück aber nicht in der richtigen Rheinfolge.

Person zum Skill-Rating

              

                Skill 1     Skill 3

Perso1           100        80

Perso2           100        60

in disem Fall sollte Perso1 zuerst ausgegeben werden, ich bekomme aber Perso2 als erstes. Und das nur wenn ich mir noch die Daten der Person über die Personentabelle via LEFT JOIN hole, ohne das LEFT JOIN ist es umgekehrt - was ich aber eher als Zufall ansehen. Ich vermutte das, dass Order by beim

AND persoID IN (SELECT persoID FROM crosstable  WHERE SkillID = 3 ORDER BY rating desc)

greift . bin am verzweifeln

[Termi]

Hm.. wenn SQL Fragen o.ä. im Raum stehen wünsche ich mir immer die Datenbank und MySQL.exe zu haben und einfach auszuprobieren - das klappt bei mir finde ich am besten .

Aber naja, BTT:

Also ich hab mir jetzt mal nebenbei bei phpmyadmin(demo seite) die Datenbank hingebaut und nen paar test datensätze eingefügt und das unten aufgeführte Statement funktioniert bei mir. Gib mir mal bitte rückmeldung darüber obs geht oder nicht.

Ich werde wahrscheinlich morgen erst wieder antworten können von daher schonmal viel Erfolg!

SELECT c.persoID, c.skillID, c.rating

 FROM 

crosstable c

 LEFT JOIN 

person p

 ON 

c.persoID = p.persoID 

WHERE 

(SELECT cs.skillID FROM crosstable cs WHERE cs.persoID = c.persoID) = 1 OR 2 ORDER BY c.rating DESC

Lg

E: Hier mal der Link zu der testDB(per Bearbeiten bei dem statement kannst du das ausführen).

Bearbeitet 5. Oktober 2011 von Termi

[Pixie]

Person zum Skill-Rating

              

                Skill 1     Skill 3

Perso1           100        80

Perso2           100        60

Soll die Anzahl Skills eigentlich irgendwann variabel sein? Dann läuft es auf eine Pivotfunktion hinaus. Ich denke aber nicht, dass MySQL die von Haus aus mitbringt.

[MadCro]

Hi Termin,

danke für den Aufwand und die Unterstützung.

Komme leider auf die Demo DB nicht drauf, hab keine zugangdaten und erkenne aktuell auch nicht wo ich mich da anmelden kann.

Das Query funkzt leider nicht. "1 OR 2" - ich muss hier ein AND haben, also die Person muss sowohl die Skill 1 als AUCH die Skill 2 zugewiesen haben, und dann für beide Skills geordnet nach Rating absteigend.

@Pixie - es sind jetzt schon mehrere Skills - danke für den Hinweis mit "pivot" schaue ich mir an

[Termi]

Hey,

leider kann ich jetzt erst reingucken, hatte heute morgen ein bisschen viel um die Ohren.

Das mit dem OR hab ich irgendwie nen bisschen verpeilt, sry dafür. Auf die DemoDB kommst du mit User 'root' und ohne pw drauf(die Datenbanken werden täglich gelöscht..).

So.. jetzt gehts aber, hoffe ich. Bei der TestDB klappt das so.. Naja, probiers doch einfach mal aus!

Du kannst dir ja auch mal die DB angucken ob ich die Struktur auch richtig aufgebaut habe und die (dummy)-Daten wie in echt sind.

SELECT c.persoID, c.skillID, c.rating

 FROM 

crosstable c

 LEFT JOIN 

personen p

 ON 

c.persoID = p.persoID 

WHERE 

1 IN (SELECT cs.skillID FROM crosstable cs WHERE cs.persoID = c.persoID) AND 3 IN (SELECT cs.skillID FROM crosstable cs WHERE cs.persoID = c.persoID) ORDER BY c.rating DESC

Hier der neue Link zur DB.

Lg

[MadCro]

Hej Termin,

Du bist ja der hammer - danke für Deinen support !

Leider konnte ich mit Deinem Query nicht das gewünschte Ergebniss auf meiner "reellen" Datenbank nachstellen (obwohl Deine dummy DB die Umgebung abbildet).

Meine aktuelle Lösung sieht nun so aus:

SELECT* 

FROM crosstable AS t1, crosstable AS t2

LEFT JOIN personen ON t2.persoID = personen.persoID

WHERE t1.persoID = t2.persoID

AND t1.SkillID =1

AND t2.SkillID =3

ORDERBY t1.rating DESC , t2.rating DESC 

LIMIT 0 , 30

funkzt auch ganz gut mit 2 eigenschaften (Skills), muss mal schauen wir es dann wird wenn man mehr als die zwei Skills berücksichtigen muss.

Wenn Jemand Optimierungs-möglichkeiten sieht - gerne !

Danke für Euere Unterstützung !

[Termi]

Hey,

gerne - dafür ist das Forum ja da!

Ah ok, jetzt seh ich auch wie du die Darstellung gerne hättest.

Wie benutzt du das Query eig.? Führst du das immer per Hand aus oder Verwendest du es aus einem Programm heraus?

Bei letzterem würde ich mir das Query dann einfach immer zusammenbasteln. Je nachdem wieviele skills du anzeigen willst veränderst du das Query einfach, z.B. mit 3 skills wüde das so aussehen:

SELECT t1.persoID, t1.skillID, t1.rating, t2.persoID, t2.skillID, t2.rating, t3.persoID, t3.skillID, t3.rating

FROM crosstable AS t1, crosstable AS t2

JOIN crosstable AS t3

LEFT JOIN personen ON t2.persoID = personen.persoID

WHERE t1.persoID = t2.persoID

AND t1.SkillID =1

AND t2.SkillID =3

AND t3.SkillID=4

ORDER BY t1.rating DESC , t2.rating DESC 

LIMIT 0 , 30

Wenn es dein Programm erledigt und verarbeitet dann arbeite IMMER! ohne *, dasonst dein Programm probleme kriegen kann wenn in der Datenbank z.B. ein neues Feld angelegt wird o.ä. bzw. verursacht es unnötigen Traffic.

Lg

P.S.: Ich heiße 'Termi' und nicht 'Termin'

[Jejerod]

Wie benutzt du das Query eig.? Führst du das immer per Hand aus oder Verwendest du es aus einem Programm heraus?

Bei letzterem würde ich mir das Query dann einfach immer zusammenbasteln.

Nach den ganzen Tagen, die der Thread schon läuft, hätte ich die Logik schon lange in die Software gepackt anstelle den perfekten Query zu suchen. Wenn man einen Query umständlich zusammenbauen muss ist das vermutlich auch der bessere Weg. Lieber frisst das Programm CPU Zeit für die Auswertung als für den Querybau.

Man möchte zwar soviel wie Möglich direkt auf der Datenbank machen, aber wenn es 3+ Tage dauert den Query zu finden ist auch mal Schluss. Das muss der Kunde schließlich auch alles Bezahlen.

Außerdem: Werden MySQL-Spezifische Queries gebaut, ist das migrieren auf andere Datenbanken wieder schwerer. Lässt sich das Problem nicht mit Standard SQL erschlagen und plant man eine Erweiterung auf andere DB Backends sollte die Logik eh besser in der Software liegen.

Hängt natürlich auch an den Anforderungen. Ist das ganze Zeitkritisch, will man eher den "perfekten Query".

Wenn es dein Programm erledigt und verarbeitet dann arbeite IMMER! ohne *, dasonst dein Programm probleme kriegen kann wenn in der Datenbank z.B. ein neues Feld angelegt wird o.ä. bzw. verursacht es unnötigen Traffic.

Korrekt. Full SELECTs sind Pflicht. Lieber schmeißt dein Programm einen Fehler als Unfug zu verarbeiten.