Abfrage-Problem

Alles, was MariaDB und MySQL betrifft, kann hier besprochen werden.

Abfrage-Problem

Postby Knight1 » 16. April 2008 13:39

Hi AF-Com,
ich habe da mal wieder ein Problem beim zusammenstellen eines SQL-Statements.

Also, ich habe da eine Tabelle wo alle Mitglieder drin stehen. Diese Tabelle enthält, neben den Feldern für die Stammdaten und einige Persönliche Einstellungen, auch ein Feld für die Positionen innerhalb des Vereins. Diese Feld heißt 'club_positions' (natürlich ohne die Hochkommata). Da ein Mitglied mehrere Positionen (theoretisch beliebig viele) innerhalb des Vereins haben kann speichere ich, für den Fall dass es mehrere Positionen sind, die entsprechenden ID's durch normale Kommata getrennt in dem Feld.

Die einzelnen Vereins-Positionen speichere ich in der Tabelle 'club_positions'.
Diese Tabelle hat folgende Felder:
id [auto_increment]
position_name
position_type [0 = Vorstand; 1 = Trainer]

Da ich sowohl die Positionen des Vereins-Vorstandes als auch die der Trainer in der Tabelle speichere, habe ich das Feld 'position_type' eingefügt um eine Unterscheidung zwischen Vorstand und Trainer treffen zu können.

Wenn ich jetzt folgendes SQL-Statement ausführen lasse
Code: Select all
SELECT * FROM
`".MYSQL_TABLE_MEMBERS."`
WHERE
(
`club_positions` LIKE (SELECT id FROM `".MYSQL_TABLE_CLUB_POSITIONS."` WHERE (`position_type` = '1'))
)

bekomme ich die, korrekten, Fehlermeldung dass das Sub-Query mehr als ein Result-Set zurückliefert.
Ich habe aber keine Ahnung wie ich das Gesammt-Statement schreiben muss um nur die Trainer geliefert zu bekommen.
Ich bin nicht der SQL-Kenner. Daher finde ich auch nicht das passende im MySQL-Manual.

Bitte helft mir.

Danke schonmal im vorraus.



Kai aka Knight1
Image
User avatar
Knight1
 
Posts: 310
Joined: 18. October 2003 10:03
Location: Trier
Operating System: Windows 7 Ultimate x64

Postby Hanspeter Thöni » 16. April 2008 15:54

Tja, dies ist der typische Fall einer n:m beziehung.
Will heissen: Das Mitglied kann mehrere Positionen haben und die Position kann mehrere Mitglieder haben.

Für diesen Fall werden 3 Tabellen benötigt:

Code: Select all
Mitglieder           Zuordnungtabelle                Positionen
----------           ----------------                ----------
Mitglied_ID    -->   Mitglied ID            /-->     Position_ID
Mitglied_Name        Position_ID         --/         Position_Bezeichnung
usw.                                                 usw.....
.....


Somit könnnen mit der Zuordnungtabelle die Mitglieder den Funktionen zugeordnet werden. Hat ein Mitglied mehr als eine Funktion gibt es einfach in der Zuordnungtabelle mehrere Sätze.

Beim Select wird dann einfach mittels Join über die 3 Tabellen abgefragt.

Dein Ansatz bringt (wie du ja gemerkt hast) grosse Probleme beim Select.
Gruss Hanspeter
User avatar
Hanspeter Thöni
 
Posts: 233
Joined: 17. August 2004 10:29
Location: Schweiz

Postby Knight1 » 17. April 2008 08:46

Danke Hanspeter,
das war die Lösung.

Habe noch eine Zuordnungstabelle angelegt wo dann nur die jeweiligen ID's gespeichert werden.

Die Abfrage habe ich nun wiefolgt gebaut:
Code: Select all
SELECT
*
FROM
`".MYSQL_TABLE_MEMBERS."`
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`
ON
(`".MYSQL_TABLE_MEMBERS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`member_id`)
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`position_id`)
WHERE
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_type` = '1')


Allerdings habe ich noch das Problem dass diese Abfrage mir die beiden Trainier so oft zurück liefert wie sie Trainerpositionen haben.
Ich weiß dass man mehrere Identische Datensätze in einem Result-Set mit DISTINCT zu einem zusammenfassen kann. Leider habe ich keinen Plan wo ich das DISTINCT einbauen muss.

Vielleicht kann mir ja jemand einen Tipp geben.


Kai aka Knight1
Image
User avatar
Knight1
 
Posts: 310
Joined: 18. October 2003 10:03
Location: Trier
Operating System: Windows 7 Ultimate x64

Postby Hanspeter Thöni » 17. April 2008 11:11

Ach so, ein Mitglied hat also mehrere Trainerpositionen.

Wenn du also jede Person nur einmal haben willst, darfst du die Trainerpositionen nicht mitausgeben, sonst gibt es immer mehrere Sätze für die gleiche Person. Distinct eliminiert Zeilen nur, wenn alle ausgegebenen Felder identisch sind. Daher wird ein Select <table>.* anstelle des Select * benötigt.

Beispiel mit Distinct
Code: Select all
SELECT
DISTINCT mysql_table_members.*
FROM  MYSQL_TABLE_MEMBERS
LEFT JOIN MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS
ON (MYSQL_TABLE_MEMBERS.id = MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS.member_id)
LEFT JOIN MYSQL_TABLE_CLUB_POSITIONS
ON (MYSQL_TABLE_CLUB_POSITIONS.id = MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS.position_id)
WHERE
(MYSQL_TABLE_CLUB_POSITIONS.position_type = '1')

Damit hast du nur die Felder aus der Tabelle Mysql_Table_Members und die doppelten werden mittels DISTINCT Eliminiert.

Alternativ wäre auch eine Subquery möglich:
Code: Select all
SELECT * FROM MYSQL_TABLE_MEMBERS
WHERE id in
(SELECT member_id FROM mysql_table_club_position_members_allocations
 LEFT JOIN
 MYSQL_TABLE_CLUB_POSITIONS
 ON (MYSQL_TABLE_CLUB_POSITIONS.id` = MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS.position_id`)
 WHERE
 (MYSQL_TABLE_CLUB_POSITIONS.position_type = '1')
)


Hier werden zuerst in der Subquery die zutreffenden Member_id(s) gesucht, und dann mittels dieser die Hauptquery durchgeführt.

Achtung: Ich habe in den Beispielen das ganze so geschreiben, als wären es Tabellennamen, da dein SQL Code mit den Stringverknüpfungen auf Konstanten nicht besonders gut lesbar ist. Es soll ja auch nur das Prinzip aufzeigen.

Uebrigens: Wenn du in der Tabelle mysql_table_members die ID auch member_id und in der Tabelle mysql_table_club_position die id auch position_id nennen würdest (siehe mein Beispiel im vorherigen Beitrag), kannst du NATURAL JOIN machen, und hast es daher viel einfacher.
Gruss Hanspeter
User avatar
Hanspeter Thöni
 
Posts: 233
Joined: 17. August 2004 10:29
Location: Schweiz

Postby Xardas der Dunkle » 17. April 2008 12:17

Achtung: Ich habe in den Beispielen das ganze so geschreiben, als wären es Tabellennamen, da dein SQL Code mit den Stringverknüpfungen auf Konstanten nicht besonders gut lesbar ist. Es soll ja auch nur das Prinzip aufzeigen.


Er ist auch so schlecht lesbar ;). Ich empfehle den Einsatz von Aliasen für die Tabellennamen o_Ô.
User avatar
Xardas der Dunkle
 
Posts: 482
Joined: 09. March 2008 19:40
Location: /var/www

Postby Hanspeter Thöni » 17. April 2008 12:23

Xardas der Dunkle wrote:Ich empfehle den Einsatz von Aliasen für die Tabellennamen


Nun ja, aber für jemanden der mit SQL noch nicht so vertraut ist macht es das ganze noch schlechter verständlich als mit den tabellennamen.
Gruss Hanspeter
User avatar
Hanspeter Thöni
 
Posts: 233
Joined: 17. August 2004 10:29
Location: Schweiz

Postby Knight1 » 23. April 2008 13:15

Hallo Hanspeter, hallo Xardas der Dunkle,
sorry dass ich erst jetzt antworte.

Nun zu Deinem Beispiel Hanspeter. Das mit dem DISTINCT kannte ich schon generell. Nur wusste ich nicht dass ich ihm explizit die Tabelle noch mitgeben musste.

Meine Abfrage sieht nun folgendermaßen aus:
Code: Select all
SELECT
DISTINCT `".MYSQL_TABLE_MEMBERS."`.*
FROM
`".MYSQL_TABLE_MEMBERS."`
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`
ON
(`".MYSQL_TABLE_MEMBERS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`member_id`)
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`position_id`)
WHERE
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_type` = '1')
ORDER BY
`".MYSQL_TABLE_MEMBERS."`.`surname` ASC, `".MYSQL_TABLE_MEMBERS."`.`firstname` ASC


Ich habe wie man sehen kann noch einen ORDER BY hinzugefügt. Dieses Statement funktioniert einwandfrei.

Das ID-Feld der Mitglieder-Tabelle in member_id umzubennen halte ich, in meinem Fall, für nicht Konsequent. Ich habe in allen Tabellen (mittlerweile sind es 58) ein Feld id welches auf auto_increment und Primary Key steht. Wenn ich jetzt anfange in einzelnen Tabellen dieses entsprechende Feld umzubennen, geht mir meine logische Struktur flöten.

Jetzt habe ich noch ein Problem.

Ich habe mir mir der obigen Abfrage alle Mitglieder zurück geben lassen welche eine Trainier-Position innerhalb des Vereins haben.
Jetzt möchte ich noch die zugehörigen Positionen bei dem entsprechenden Mitglied ausgeben lassen. Ich bin da schon seit Stunden am grübeln wie ich das machen kann ohne zwei oder mehr einzelne Schleifen mit jeder menge IF-Bedingungen in PHP schreiben zu müssen. Diese Arbeit soll MySQL übernehmen.

Wenn ich folgendes Statement mache, bekomme ich einen Fehler:
Code: Select all
SELECT
`".MYSQL_TABLE_CLUB_POSITIONS."`.*
FROM
`".MYSQL_TABLE_CLUB_POSITIONS."`
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`member_id` = '{$trainer['id']}')
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`position_id`)
WHERE
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_type` = '1')

Der Fehler lautet: Not unique table/alias: 'tce__club_positions'
Irgendwie habe ich noch ein Verständnis-Problem.

Kann mir das mal bitte jemand erklären?


Danke schon im vorraus.


Kai aka Knight1
Image
User avatar
Knight1
 
Posts: 310
Joined: 18. October 2003 10:03
Location: Trier
Operating System: Windows 7 Ultimate x64

Postby Hanspeter Thöni » 23. April 2008 13:46

Jetzt wird's ein bisschen knifflig.

Deine Query führt zum Fehler, da du die Tabelle positions 2x drin hast, das ist zwar grundsätzlich möglich, aber dann musst du mindestens einer der beiden einen Alias geben, damit SQL die beiden Tabellen in der Folge unterscheiden kann.

Eigentlich hast du ja das vorher schon gehabt (ohne Distinct) da bekommt du jeder Persion mit jeder Trainerposition. Es gibt eigentlich nur 2 Möglichkeiten:

Ausgangslage: Tabelle member
Code: Select all
ID       Name
--------------------
1        meier
2        Müller


Ausgangslage: Tabelle position
Code: Select all
ID       Name
--------------------
1        jugendtrainer
2        erwachenentrainer
3        Spezial


Ausgangslage: Tabelle Zuordnung
Code: Select all
member_id  position_id
--------------------
1          1
1          2
2          1
2          3



Die Frage ist jetzt wie soll deine Ausgabe in PHP schlussendlich aussehen.

1. Möglichkeit
Wenn du mit distinct nur auf die namen selektiert bekommst du folgende:
Code: Select all
1      Meier
2      Müller


Wenn du jetzt in PHP die Positionen mitausgeben willst, musst du für jeden name (member_id) eine separate Query auf Zuordnung/position machen. (In Schleife Memberausgabe)

2. Möglichkeit
Das war genau das, was du vorher ohne Distinct gehabt hast.
Code: Select all
1     Meier        jugendtrainer
1     Meier        erwachsenentrainer
2     Müller       jugendtrainer
2     Müller       Spezial

Dann hast du die Namen mehrfach.
Eine andere Lösung sehe ich in SQL nicht.

Du musst deinem PHP-Code halt beibringen, den Namen erst wieder auszugeben, wenn die member_id wechselt! (Sort vorausgesetzt).
Gruss Hanspeter
User avatar
Hanspeter Thöni
 
Posts: 233
Joined: 17. August 2004 10:29
Location: Schweiz

Postby Knight1 » 23. April 2008 14:58

Hanspeter Thöni wrote:...Wenn du jetzt in PHP die Positionen mitausgeben willst, musst du für jeden name (member_id) eine separate Query auf Zuordnung/position machen. (In Schleife Memberausgabe)
...


Auf die Idee bin ich auch schon gekommen. Ich habe aber, wie gesagt, keine Ahnung wie ich den Query bauen muss um aus einer Kombination beider Tabellen (Zuodnungstabelle und Positionstabelle) die entsprechende Ausgabe zu erhalten.
Meine momentane Ausgabe sieht folgendermaßen aus: Bild
Jetzt möchte ich halt dass neben dem Begriff "Trainer-Position(en)" die entsprechend zugeordneten Positionen erscheinen. Es können, wie in meinem ersten Post schon gesagt, mehrere sein.

Das Statement müsste doch irgendwie folgendermaßen lauten: Liefere mir die Trainer-Positionen welche diesem Mitglied zugeordnet sind.

Wie Du, Hanspeter, schon korrekt angemerkt hast, bin ich mit MySQL noch nicht so in der Tiefe vertraut. Hatte bisher nur einfachste SELECTS, UPDATES und DELETES.

Kai aka Knight1
Image
User avatar
Knight1
 
Posts: 310
Joined: 18. October 2003 10:03
Location: Trier
Operating System: Windows 7 Ultimate x64

Postby Nobbie » 23. April 2008 18:39

Im Prinzip müßte das mit der MySQL-Funktion GROUP_CONCAT() funktionieren. Lies mal http://dev.mysql.com/doc/refman/5.1/en/ ... oup-concat
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Postby Knight1 » 25. April 2008 07:30

Danke für Eure Antworten.

Mittlerweile funktioniert alles einwandfrei. Auch dank Eurer Hilfestellungen.

Für die Darstellung der Mitglieder welche eine (oder mehrere) Trainer-Position(en) haben nutze ich folgendes Statement:

Code: Select all
SELECT
DISTINCT `".MYSQL_TABLE_MEMBERS."`.*
FROM
`".MYSQL_TABLE_MEMBERS."`
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`
ON
(`".MYSQL_TABLE_MEMBERS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`member_id`)
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`position_id`)
WHERE
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_type` = '1')
ORDER BY
`".MYSQL_TABLE_MEMBERS."`.`surname` ASC,
`".MYSQL_TABLE_MEMBERS."`.`firstname` ASC


Wobei die Ziffer 1 in der WHERE-Klausel für den Positions-Typ Trainer steht.

Für die Übersicht des Vorstandes nutze ich folgendes Statement:

Code: Select all
SELECT
DISTINCT `".MYSQL_TABLE_MEMBERS."`.*
FROM
`".MYSQL_TABLE_MEMBERS."`
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`
ON
(`".MYSQL_TABLE_MEMBERS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`member_id`)
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`position_id`)
WHERE
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_type` = '0')
ORDER BY
`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_name`,
`".MYSQL_TABLE_MEMBERS."`.`surname` ASC,
`".MYSQL_TABLE_MEMBERS."`.`firstname` ASC


Auch hier gilt: Ein Mitglied kann theoretisch mehrere Vorstands-Positionen haben.

Um mir nun die zugehörigen Positionen bei dem jeweiligen Mitglied ausgeben zu lassen, nutze ich folgendes Statement:

Code: Select all
SELECT
DISTINCT `".MYSQL_TABLE_CLUB_POSITIONS."`.*
FROM
`".MYSQL_TABLE_CLUB_POSITIONS."` AS `positions`
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`member_id` = '{$trainer['id']}')
LEFT JOIN
`".MYSQL_TABLE_CLUB_POSITIONS."`
ON
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`id` = `".MYSQL_TABLE_CLUB_POSITIONS_MEMBERS_ALLOCATIONS."`.`position_id`)
WHERE
(`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_type` = '1')
ORDER BY
`".MYSQL_TABLE_CLUB_POSITIONS."`.`position_name`


Jeweils mit der Ziffer 1 für Trainer oder 0 für Vorstand in der WHERE-Klausel.
Ich habe auch mittlerweile heruasgefunden an welcher Stelle ich einen Alias setzen muss damit diese Abfrage funktioniert.
Ich nutze die Konstanten um die Tabellennamen nur einmal notieren zu müssen. Ich habe da eine zentrale Datei in welcher diese Konstanten definiert werden. Sollte sich ein Tabellenname oder das von mir genutzte Präfix ändern, brauche es nur an ein Stelle zu ändern und muss nicht eventuell Dutzende von Dateien durchforsten. Ausserdem sind Konstanten , im gegensatz zu Variablen, nicht Lösch- bzw. Überwschreibbar. Das gibt mir in diesem Punkt ein gewisse Script-Sicherheit. Natürlich muss ich noch bei GET- bzw. POST-Daten prüfen.

Also, nochmals Danke an alle die mir mit Tipps zur Seite gestanden sind.


Kai aka Knight1
Image
User avatar
Knight1
 
Posts: 310
Joined: 18. October 2003 10:03
Location: Trier
Operating System: Windows 7 Ultimate x64


Return to MariaDB - MySQL

Who is online

Users browsing this forum: No registered users and 7 guests