PDA

Archiv verlassen und diese Seite im Standarddesign anzeigen : SQL Aufgabe: Wie realisieren?


AtTheDriveIn
2009-11-30, 15:18:29
Hi,

ich stehe gerade vor einem kniffeligen Problem, was ich elegant mit SQL gelöst haben möchte.

Vereinfacht lässt es sich durch folgendes Beispiel beschreiben:

Ich habe zwei Tabellen AbteilungsPersonal und MitarbeiterAuswahl


table AbteilungsPersonal
(
Abteilungs_id,
Mitarbeiter_id
)

table MitarbeiterAuswahl
(
Mitarbeiter_id
)



Ich will nun genau die Abteilung haben, in der alle Mitarbeiter arbeiten, die in MitarbeiterAuswahl eingetragen sind.

Hat jemand einen Vorschlag?

Edit: DBS ist MS SQL 2005, also muss das ganze in T-SQL funktionieren
Edit: In den Ergebnis Abteilungen können auch noch andere Mitarbeiter arbeiten. Und Mitarbeiter können in einer Abteilung mehrfach vorkommen.

Henroldus
2009-11-30, 15:23:45
select Abteilungs_id
from AbteilungsPersonal A, table MitarbeiterAuswahl M
where A.Mitarbeiter_id = M.Mitarbeiter_id

daflow
2009-11-30, 15:32:27
Kann ein Ma in deinem Modell in meheren Abteilungen sein?

AtTheDriveIn
2009-11-30, 15:34:50
select Abteilungs_id
from AbteilungsPersonal A, table MitarbeiterAuswahl M
where A.Mitarbeiter_id = M.Mitarbeiter_id
nein, das geht nicht. Damit bekomme ich die Abteilungen, welche schon einen der Mitarbeiter haben, aber nicht nur die, die alle haben.

AtTheDriveIn
2009-11-30, 15:36:02
Kann ein Ma in deinem Modell in meheren Abteilungen sein?

ja

daflow
2009-11-30, 15:40:45
ja

Mhm, und in der MA-Auswahl Tabelle sind also auch Mitarbeiter drin die in mehreren Abteilungen arbeiten, right? Und du willst jetzt nur ein ergebniss, wenn es eine Abterilung gibt in der jeder einzelne aus der MA-Auswahl Tabelle drin ist? Könnten ja wenn blöd läuft auch 2 oder mehr Abteilungen sein, je nachdem was in MA-Auswahl drin steht?

AtTheDriveIn
2009-11-30, 15:44:49
Mhm, und in der MA-Auswahl Tabelle sind also auch Mitarbeiter drin die in mehreren Abteilungen arbeiten, right? Und du willst jetzt nur ein ergebniss, wenn es eine Abterilung gibt in der jeder einzelne aus der MA-Auswahl Tabelle drin ist? Könnten ja wenn blöd läuft auch 2 oder mehr Abteilungen sein, je nachdem was in MA-Auswahl drin steht?

Ganz genau. :)

Hardwaretoaster
2009-11-30, 15:45:08
also wirklich alle aus der Auswahl?
Ungefähr so?

select A.Abteilungs_id
from AbteilungsPersonal A, table MitarbeiterAuswahl M
group by A.Abteilungs_id
having count() = count(M.Mitarbeiter_id)

AtTheDriveIn
2009-11-30, 16:01:09
also wirklich alle aus der Auswahl?
Ungefähr so?

select A.Abteilungs_id
from AbteilungsPersonal A, table MitarbeiterAuswahl M
group by A.Abteilungs_id
having count() = count(M.Mitarbeiter_id)


Die Abfrage verstehe ich so nicht ganz.

Liefert alle Abteilungs_id die so oft vorkommen wie Mitarbeiter_id in M sind?

Hardwaretoaster
2009-11-30, 16:10:19
Es kann sein, dasses genau so in der Praxis nicht geht (hab' aus der Luft geschrieben), aber die idee dahinter ist:

Gruppiere die Abteilungen nach id und das count in having zählt die Datensätze, die innerhalb der Gruppe sind. Wenn jetzt kein Mitarbeiter zweimal in einer Abteilung ist (was die Schreibweise mit den ids nicht vermuten lässt, ich schätze du hast entsprechnd Schüssel angelegt), dann liefert der Vergleich mit der Anzahl aller ausgewählten Mitarbeiter die Abteilungen (deren IDs), welche alle Mitarbeiter enthalten.

AtTheDriveIn
2009-11-30, 16:24:44
Es kann sein, dasses genau so in der Praxis nicht geht (hab' aus der Luft geschrieben), aber die idee dahinter ist:

Gruppiere die Abteilungen nach id und das count in having zählt die Datensätze, die innerhalb der Gruppe sind. Wenn jetzt kein Mitarbeiter zweimal in einer Abteilung ist (was die Schreibweise mit den ids nicht vermuten lässt, ich schätze du hast entsprechnd Schüssel angelegt), dann liefert der Vergleich mit der Anzahl aller ausgewählten Mitarbeiter die Abteilungen (deren IDs), welche alle Mitarbeiter enthalten.

Wenn ich nur eine Spalte selektiere und danach dann gruppiere und das dann zähle ist es immer 1 oder irre ich mich da.

Du hast mich aber auf was Wichtiges aufmerksam gemacht. Mitarbeiter können mehrmals in einer Abteilung vorhanden sein. Das macht in dem Beispiel zwar keinen Sinn, aber in meinem realen Fall schon.

Edit: Denkfehler

CrazyIvan
2009-11-30, 16:35:25
with MA as (
select count(*) AnzMitarbeiter
from mitarbeiterauswahl
)

select abteilungsid
from abteilungspersonal ap
group by abteilungsid
having count(distinct ap.mitarbeiter_id)=MA.AnzMitarbeiter

Abgesehen von Tippfehlern sollte das so passen. Count(distinct) zählt nur die verschiedenen IDs, sodass Doppelnennungen kein Problem sind. Nach "WITH" folgt eine CTE, die die Gesamtanzahl der Mitarbeiter zurückgibt.

AtTheDriveIn
2009-12-01, 09:08:41
with MA as (
select count(*) AnzMitarbeiter
from mitarbeiterauswahl
)

select abteilungsid
from abteilungspersonal ap
group by abteilungsid
having count(distinct ap.mitarbeiter_id)=MA.AnzMitarbeiter

Abgesehen von Tippfehlern sollte das so passen. Count(distinct) zählt nur die verschiedenen IDs, sodass Doppelnennungen kein Problem sind. Nach "WITH" folgt eine CTE, die die Gesamtanzahl der Mitarbeiter zurückgibt.

Hm...aber ich kann nicht einfach die Anzahl vergleichen. Jede Abteilung hat x Mitarbeiter und wenn darunter die Gesuchten sind (wie gesagt ist sogar eine mehrfache Eintragung von Mitarbeiter in einer Abteilung möglich) dann soll die Abteilung ausgegeben werden.

Ich weiß das mit dem mehrfach eingetragenen Mitarbeiter in einer Abteilung macht real keinen Sinn, aber da das Beispiel nur verdeutlichen sollte was mein Problem ist, muss man das mal schlucken. :)

Beispiel:

Abteilung [A_ID, Mitarbeiter_ID]
(
1,14
1,12
1,5
1,12
1,4
2,6
2,14
2,6
2,12
)

Wenn MitarbeiterAuswahl nun folgendermaßen aussieht
(
6
14
)

dann lautet das richtige Ergebnis 2.
Wenn ich nach [12 14] suche dann ist das Ergebnis 1,2


Mengenmäßig betrachtet suche ich in verschiedenen Mengen eine Teilmenge.

daflow
2009-12-01, 09:54:54
Hm...aber ich kann nicht einfach die Anzahl vergleichen. Jede Abteilung hat x Mitarbeiter und wenn darunter die Gesuchten sind (wie gesagt ist sogar eine mehrfache Eintragung von Mitarbeiter in einer Abteilung möglich) dann soll die Abteilung ausgegeben werden.
[...]


"distinct" sollte da helfen, s.o. ;)

wry
2009-12-01, 13:11:17
Ich glaub was du suchst ist die "Relationale Division"

Mehr darüber hier:
http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Hab hier die Division mit dem differenz Operator aus obiger Quelle schnell umgeschrieben, allerdings ungetestet :biggrin:

SELECT DISTINCT Abteilung_id FROM AbteilungsPersonal AS AP1
WHERE (SELECT Mitarbeiter_id FROM MitarbeiterAuswahl
EXCEPT
SELECT Mitarbeiter_id
FROM AbteilungsPersonal AS AP2
WHERE AP1.Mitarbeiter_id = AP2.Mitarbeiter_id) IS NULL;

CrazyIvan
2009-12-01, 18:26:44
@TheMarsVolta/Sparta

Hast recht. War ein kleiner Denkfehler meinerseits. Natürlich gehört da noch ein JOIN dazu:

with MA as (
select count(*) AnzMitarbeiter
from mitarbeiterauswahl
)

select ap.abteilung_id
from abteilungspersonal ap
inner join mitarbeiterauswahl m on ap.mitarbeiter_id = m.mitarbeiter_id
group by ap.abteilung_id
having count(distinct ap.mitarbeiter_id)=MA.AnzMitarbeiter
Damit bekommst Du erstmal alle Zeilen aus AbteilungPersonal, die mit einem der Mitarbeiter in Mitarbeiterauswahl verbunden sind. Durch das having werden nur die Abteilungen genommen, die genau so viele verschiedene Mitarbeiter haben, wie in mitarbeiterauswahl. Sollten auch in mitarbeiterauswahl Duplikate sein, kannste in der CTE statt count(*) auch count(distinct mitarbeiter_id) nehmen. Obiger Code geht davon aus, dass "Mitarbeiter" eine Stammdatentabelle ist, in der jeder Mitarbeiter nur genau einmal vorkommt.