SQL - Krankenhaus, 2x Mitarbeiter auf selber Station in einer Zeile ausgeben

[buttervolk]

Hallo zusammen

ich hänge seit Stunden an einem wahrscheinlich einfach zu lösendem Problem.

Ich habe ein DB in MySQLWorkbench mit den Tabellen Mitarbeiter, arbeitet und Stationen.
Also eine n:m, Mitarbeiter - Stationen.

Es sollen alle Paare von Mitarbeitern die auf der selben Station arbeiten ausgeben werden. Jedes paar von Mitarbeitern soll nur 1x ausgegeben werden.

Wie kann ich 2 Mitarbeiter in einer Zeile ausgeben, ist das überhaupt mögl.?

Hier die Tabellen mit bsp. Datensätzen:

Mitarbeiter
PersonalNr | Nachname
123 | Müller
124 | Meier
125 | Schmidt

arbeitet
PersonalNr | StationsNr
123 | A1
124 | A1
125 | A2

Stationen
StationsNr | Bezeichnung
A1 | Zentrale Notaufnahme
A1 | Zentrale Notaufnahme
A2 | Neurologische Intensivstation

Ergebnis sollte so aussehen:
1. PersonalNr | 1. Nachname | 2. PersonalNr | 2. Nachname | StationsNr | Bezeichnung
123 | Müller | 124 | Meier | A1 | Zentrale Notaufnahme

Hier mein verzweifelter Versuch:
SELECT DISTINCT MitarbeiterNr1.PersonalNr AS '1.PersonalNr', MitarbeiterName1.Nachname AS '1.Nachname', MitarbeiterNr2.PersonalNr AS '2.PersonalNr', MitarbeiterName2.Nachname AS '2.Nachname', Stationen.StationsNr, Stationen.Bezeichnung
FROM Mitarbeiter AS MitarbeiterNr1
INNER JOIN Mitarbeiter AS MitarbeiterNr2
ON MitarbeiterNr1.PersonalNr=MitarbeiterNr2.PersonalNr
INNER JOIN Mitarbeiter AS MitarbeiterName1 ON MitarbeiterName1.PersonalNr = MitarbeiterNr2.PersonalNr
INNER JOIN Mitarbeiter AS MitarbeiterName2 ON MitarbeiterName2.PersonalNr = MitarbeiterNr1.PersonalNr
INNER JOIN arbeitet ON MitarbeiterNr1.PersonalNr = arbeitet.PersonalNr
INNER JOIN Stationen ON arbeitet.StationsNr = Stationen.StationsNr
GROUP BY Stationen.StationsNr, Stationen.Bezeichnung

LG Butter

 

[akretschmer]

das wird so nix, weil Du nicht wissen kannst, wie viele Mitarbeiter max. auf einer Station arbeiten. Das kann sich ja ändern.

test=*# select * from mitarbeiter ;
 nr  |  name   
-----+---------
 123 | müller
 124 | maier
 125 | schmidt
(3 rows)

test=*# select * from arbeitet ;
 nr  | station
-----+---------
 123 | a1
 124 | a1
 125 | a2
(3 rows)

test=*# select * from stationen ;
 nr |     bez     
----+-------------
 a1 | notaufnahme
 a2 | neurologie
(2 rows)

test=*# select array_agg(m.nr), array_agg(m.name), s.nr, s.bez from mitarbeiter m right join arbeitet a on a.nr=m.nr left join stationen s on a.station=s.nr group by s.nr, s.bez;
 array_agg |   array_agg    | nr |     bez     
-----------+----------------+----+-------------
 {123,124} | {müller,maier} | a1 | notaufnahme
 {125}     | {schmidt}      | a2 | neurologie
(2 rows)

test=*# select array_agg(m.nr), string_agg(m.name,', '), s.nr, s.bez from mitarbeiter m right join arbeitet a on a.nr=m.nr left join stationen s on a.station=s.nr group by s.nr, s.bez;
 array_agg |  string_agg   | nr |     bez     
-----------+---------------+----+-------------
 {123,124} | müller, maier | a1 | notaufnahme
 {125}     | schmidt       | a2 | neurologie
(2 rows)

test=*#

Mal so als Idee, die Mitarbeiter als Array oder Text zu aggregieren. PostgreSQL.

 

[akretschmer]

Um ungefähr das hinzubekommen, was Du möchtest:

test=*# with foo as (select m.nr pnr,m.name, s.nr snr, s.bez, row_number() over (partition by s.nr) r from mitarbeiter m right join arbeitet a on a.nr=m.nr left join stationen s on a.station=s.nr ) select string_agg(pnr::text,'') filter (where r = 1) as "1.pnr", string_agg(name,'') filter (where r = 1) as "1.mitarbeiter", string_agg(pnr::text,'') filter (where r = 2) as "2.pnr", string_agg(name,'') filter (where r = 2) as "2.mitarbeiter", snr as station from foo group by snr;
 1.pnr | 1.mitarbeiter | 2.pnr | 2.mitarbeiter | station
-------+---------------+-------+---------------+---------
 123   | müller        | 124   | maier         | a1
 125   | schmidt       |       |               | a2
(2 rows)

oder auch

test=*# with foo as (select m.nr pnr,m.name, s.nr snr, s.bez, row_number() over (partition by s.nr) r from mitarbeiter m right join arbeitet a on a.nr=m.nr left join stationen s on a.station=s.nr ) select string_agg(pnr::text,'') filter (where r = 1) as "1.pnr", string_agg(name,'') filter (where r = 1) as "1.mitarbeiter", string_agg(pnr::text,'') filter (where r = 2) as "2.pnr", string_agg(name,'') filter (where r = 2) as "2.mitarbeiter", snr as station, bez from foo group by snr,bez;
 1.pnr | 1.mitarbeiter | 2.pnr | 2.mitarbeiter | station |     bez     
-------+---------------+-------+---------------+---------+-------------
 123   | müller        | 124   | maier         | a1      | notaufnahme
 125   | schmidt       |       |               | a2      | neurologie
(2 rows)

 

[buttervolk]

Danke für deine schnelle Antwort.

Es ist davon auszugehen, das max. 2 Mitarbeiter auf einer Station arbeiten. Ändert sich nicht. Also entweder 1 oder 2 MA pro Station.
Wie kann ich als Array oder Text aggregieren? array_agg kennt mein MySQLWorkbench nicht...

 

[akretschmer]

Wie kann ich als Array oder Text aggregieren? array_agg kennt mein MySQLWorkbench nicht...

ja, MySQL kennt vieles nicht. Dafür gibt es aber eine vergleichbare Funktion, wirst du sicher via Google finden. Ich nutze kein MySQL. Aus Gründen.

 

[buttervolk]

Also ich habe nun vieles versucht um die entsprechenden Funktionen in MySQL zu finden. Ohne vollständigem Ergebnis. Viele Funktionen gibt es bei MySQL einfach nicht... MySQL: Funktionen | a coding project

Werde mich dann wohl an meinen Prof. wenden und ihn Fragen was er sich darunter vorstellt. Btw, die Aufgabe gibt nur 3 Punkte
Trotzdem, vielen Dank für deine Bemühungen!

 

[akretschmer]

Also ich habe nun vieles versucht um die entsprechenden Funktionen in MySQL zu finden. Ohne vollständigem Ergebnis.

Google: "mysql string_agg", erster Treffer.

 

[buttervolk]

row_number(), over, pnr::text, filter - kennt MySQL nicht, dazu finde ich nichts... string_agg ist klar.

Für row_number() könnte ich eine eigene Funktion schreiben, kann nicht sein das der Prof. das fordert...
row_number() gibt es ab MySQL 8.0 - wir arbeiten natürlich mit einer veralteten Version.

 

[akretschmer]

wir arbeiten natürlich mit einer veralteten Version.

armes Deutschland.

row_number() ist SQL-Standard seit Urzeiten, pnr::text ist ein CAST (kann MySQL wohl auch, nur anders), filter (where ...) ist auch SQL-Standard, kann MySQL nicht, kann man als CASE WHEN ... umschreiben.

Man könnte auch eine bessere DB nehmen. PostgreSQL kommt sogar aus dem universitären Umfeld, und kostet nichts.

 

[akretschmer]

Btw.: Respekt, daß Du in Deinem Alter noch lernst ;-)