SQL Suche über mehrere Tabellen

[Hailander]

Hi,
ich entwickele ein Konferenzsystem und arbeite gerade an der Teilnehmersuche. Sie funktioniert, aber da ich nicht so der SQL Profi bin und gerne die optimale Lösung hätte, wäre es nett, wenn jemand mal draufschauen könnte, ob das noch besser geht.

Zur Datenstruktur ( in Klammern die Tabellennamen): es gibt eine beliebige Anzahl Kurse (workshops) und eine beliebige Anzahl fest definierter Termine (slots). Die Kombination (ein Kurs findet an einem Termin statt) ist eine Verfügbarkeit (workshop_availabilities). Jeder Teilnehmer (participants) kann an einer beliebigen Anzahl Kurse, also den Verfügbarkeiten, teilnehmen, die Kombination ist eine Teilnahme (workshop_participations).

1) Suche nach allen Teilnehmern, die an einer beliebigen Verfügbarkeit zu einem festen Termin teilnehmen:

SELECT * FROM `participants` AS `p`
INNER JOIN workshop_participations AS `wsp1` ON `wsp1`.`participant_id` = `p`.`id`
INNER JOIN workshop_availabilities AS `wsa1` ON `wsp1`.`workshop_availability_id` = `wsa1`.`id`
WHERE `wsa1`.`slot_id` = 1

2) Suche nach allen Teilnehmern, die an einer bestimmten Verfügbarkeit teilnehmen:

SELECT * FROM `participants` AS `p`
INNER JOIN workshop_participations AS `wsp2` ON `wsp2`.`participant_id` = `p`.`id`
INNER JOIN workshop_availabilities AS `wsa2` ON `wsp2`.`workshop_availability_id` = `wsa2`.`id
WHERE `wsa2`.`id` = 1

3) Und jetzt meine Krampf-Lösung: suche nach allen Teilnehmern, die NICHT an einer bestimmten Verfügbarkeit teilnehmen:

SELECT * FROM `participants` AS `p`
LEFT JOIN (
  SELECT `wsp`.`id` AS wsp_id, `wsp`.`participant_id` AS participant_id
  FROM workshop_participations AS `wsp`
  INNER JOIN workshop_availabilities AS `wsa` ON `wsp`.`workshop_availability_id` = `wsa`.`id`
  WHERE `wsa`.`slot_id` = 4
) AS `wsp4` ON (`wsp4`.`participant_id` = `p`.`id`)
WHERE ISNULL(`wsp4`.`wsp_id`)

4) Und jetzt alles zusammen:

SELECT * FROM `participants` AS `p`
INNER JOIN workshop_participations AS `wsp1` ON `wsp1`.`participant_id` = `p`.`id`
INNER JOIN workshop_availabilities AS `wsa1` ON `wsp1`.`workshop_availability_id` = `wsa1`.`id`
INNER JOIN workshop_participations AS `wsp2` ON `wsp2`.`participant_id` = `p`.`id`
INNER JOIN workshop_availabilities AS `wsa2` ON `wsp2`.`workshop_availability_id` = `wsa2`.`id`
LEFT JOIN (
  SELECT `wsp`.`id` AS wsp_id, `wsp`.`participant_id` AS participant_id
  FROM workshop_participations AS `wsp`
  INNER JOIN workshop_availabilities AS `wsa` ON `wsp`.`workshop_availability_id` = `wsa`.`id`
  WHERE `wsa`.`slot_id` = 4
) AS `wsp4` ON (`wsp4`.`participant_id` = `p`.`id`)
WHERE
  `wsa1`.`slot_id` = 1
  AND `wsa2`.`id` = 1
  AND ISNULL(`wsp4`.`wsp_id`)

Meine Fragen:
- muss man bei Nr 3 wirklich eine Subquery machen?
- muss man bei Nr 4 die Tabellen wirklich mehrfach aufzählen (wsa1, wsa2, ...)?
- andere Verbesserungen, auch wenn es nur Kleinigkeiten sind?

 

[xmarsx]

Ich habe es jetzt nur überflogen, aber schau Dir mal das Schlüsselwort 'EXCEPT' an. Das gehört zu den Mengenoperationen (UNION, INTERSECT, EXCEPT):
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-EXCEPT

Das wäre dann: Gib mit alle Teilnehmer außer denen (EXCEPT) die einem Termin/Workshop zugeordnet wurden.

SELECT * FROM participants EXCEPT (SELECT ...)

Das zweite SELECT ist dann vermutlich das zweite von Dir genannte Beispiel.

 

[Hailander]

Danke schonmal. Ich arbeite mit MySQL, aber das kann so was ähnliches (kurz gesucht und zusammengebaut):

SELECT * FROM `participants` AS `p`
WHERE NOT EXISTS (
  SELECT *
  FROM workshop_participations AS `wsp`
  INNER JOIN workshop_availabilities AS `wsa` ON `wsp`.`workshop_availability_id` = `wsa`.`id`
  WHERE `wsa`.`slot_id` = 4
  AND `wsp`.`participant_id` = `p`.`id`
)

Schon etwas kürzer, aber vielleicht geht es ja noch besser

 

[narzissm]

Inwiefern willst du die optimale Lösung? Lesbarkeit/Verständnis, Performance? Eine kürzere Abfrage heißt nicht gleich, dass das die beste/schnellste/... Möglichkeit ist.

 

[xmarsx]

Eigentlich sollte es doch reichen nur auf das vorhanden sein der participant_id in workshop_participations zu schauen. Der Rest interessiert doch eigentlich nicht.

PostgreSQL kann ich nur empfehlen. Kostet auch nichts und umfasst mehr Befehle des SQL-Standards.

 

[Hailander]

Eigentlich sollte es doch reichen nur auf das vorhanden sein der participant_id in workshop_participations zu schauen.

Ne, jeder Teilnehmer kann ja mehrere participations haben. Man muss halt z.B. alle Teilnehmer suchen können, die am einen Termin einen bestimmten Workshop haben und an einem anderen Termin gar nichts. Deshalb muss man participations mit availabilities verknüpfen.

Inwiefern willst du die optimale Lösung? Lesbarkeit/Verständnis, Performance? Eine kürzere Abfrage heißt nicht gleich, dass das die beste/schnellste/... Möglichkeit ist.

Insbesondere frage ich mich, ob es bei Nr 3 nicht ohne Subquery geht. Ich arbeite mit einem Framework, und dessen Active Record die Subquery beizubringen geht zwar, ist ein ganz schöner Akt.
Ansonsten alles was du aufgezählt hast Nur dass es läuft heißt ja nicht, dass es nicht noch kürzer/eleganter oder so gehen würde.

 

[xmarsx]

Insbesondere frage ich mich, ob es bei Nr 3 nicht ohne Subquery geht. Ich arbeite mit einem Framework, und dessen Active Record die Subquery beizubringen geht zwar, ist ein ganz schöner Akt.

Du kannst ja einen View erstellen und dem unbekannten Framework den View statt der Subquery unter schieben. Evtl. macht es das etwas einfacher.