InterBase Group By / Left Join / 3xgleiche Tabelle??

[Agasch]

Moin,
versuche gerade das Gruppieren/Berechnen von Daten und das Anwenden von Joins zu erlernen. Hier stehe ich aber irgendwie auf dem Schlauch....

Habe 2 Tabellen:

Tabelle: Personal
Feld: PERSONAL_ID

Daten in der Tabelle: PERSONAL_ID=1


Tabelle: FEHLTAGE
Felder: FEHLTAGE_ID, FEHLTAGE_PERSONALID, FEHLTAGE_ARBEITSTAGE, FEHLTAGE_ART
-> ART bedeutet: U(rlaub), K(rank), F(ehlt)

Daten in der Tabelle "FEHLTAGE":

FEHLTAGE_ID FEHLTAGE_PERSONALID FEHLTAGE_ART FEHLTAGE_ARBEITSTAGE
=========== =================== ============ ====================
1 1 U 10
2 1 U 7
3 1 F 1
4 1 K 5
5 1 K 2


d.h
PERSONAL_ID hat dann: 17 Tage Urlaub, 1 Tag gefehlt und 7 Tage krank gemacht.


Ich will jetzt auf die Tabelle Fehltage 3x zugreifen:

select
P.PERSONAL_ID,
Sum(U.FEHLTAGE_ARBEITSTAGE) as URLAUB,
Sum(F.FEHLTAGE_ARBEITSTAGE) as FEHLTAGE,
Sum(K.FEHLTAGE_ARBEITSTAGE) as KRANK

from PERSONAL P
LEFT JOIN FEHLTAGE U ON (U.FEHLTAGE_PERSONALID=P.PERSONAL_ID AND U.FEHLTAGE_ART='U')
LEFT JOIN FEHLTAGE F ON (F.FEHLTAGE_PERSONALID=P.PERSONAL_ID AND F.FEHLTAGE_ART='F')
LEFT JOIN FEHLTAGE K ON (K.FEHLTAGE_PERSONALID=P.PERSONAL_ID AND K.FEHLTAGE_ART='K')
GROUP BY P.PERSONAL_ID


Richtig wäre:
PERSONAL_ID=1 URLAUB=17 FEHLTAGE=1 KRANK=7

Bekomme jedoch:
PERSONAL_ID=1 URLAUB=31 FEHLTAGE=4 KRANK=14


Setze ich meine SQL_Anweisung schrittweise ein, dh nur ein Left Join, bekomme ich den richtigen Wert, aber 2 oder 3 liefern die falschen Werte.
Wie müüste das richtig aussehen?

Danke im Voraus
Andreas

[geesmith]

Hi

Mit left join bist du ja grundsätzlich richtig unterwegs. Zeigt er dir die Werte ohne Group -By denn richtig an? Versuch mal den Group By in einem übergeordneten SELECT zu machen. Wäre möglich dass ein Group By direkt mit left join Probleme verursacht... also:

SELECT P.PERSONAL_ID, SUM(URLAUB),SUM(FEHLTAGE),SUM(KRANK)
FROM(
SELECT
P.PERSONAL_ID,
U.FEHLTAGE_ARBEITSTAGE as URLAUB,
F.FEHLTAGE_ARBEITSTAGE as FEHLTAGE,
K.FEHLTAGE_ARBEITSTAGE as KRANK

from PERSONAL P
LEFT JOIN FEHLTAGE U ON (U.FEHLTAGE_PERSONALID=P.PERSONAL_ID AND U.FEHLTAGE_ART='U')
LEFT JOIN FEHLTAGE F ON (F.FEHLTAGE_PERSONALID=P.PERSONAL_ID AND F.FEHLTAGE_ART='F')
LEFT JOIN FEHLTAGE K ON (K.FEHLTAGE_PERSONALID=P.PERSONAL_ID AND K.FEHLTAGE_ART='K')
)
GROUP BY P.PERSONAL_ID

Geht das?

[Agasch]

Hi, IntreBase unterstützt es leider nicht

[Sir Rufo]

Mit dem Alias U werden 2 Sätze angesprochen, mit F 1 und mit K 2.
Das Ergebnis, worauf dann das GROUP BY angewendet wird besteht also aus 2x2x1 = 4 Sätzen.
Kann also schon vom Ansatz nicht stimmen (das ist auch die Erklärung, warum bei Fehltagen eine 4 herauskommt, weil die 4 mal berücksichtigt/aufsummiert werden).

[jobo]

Für Übungszwecke ist der Join zur Personaltabelle vielleicht sinnvoll, technisch aber hier vollkommen unnötig.
Du benötigst nur die Fehltagetabelle für die Ausgabe, der Join zur Personaltabelle bringt keinen Mehrwert in Deinem Beispiel.

Das wäre erst gegeben, wenn Du
a) einen weiteren Wert aus Personal ausgeben willst, z.B. Name
b) eine Liste aller Personaldaten haben willst, auch wenn gar keine Fehltage vorhanden sind.

Dann würde ich jede "Einzelmenge" (die 3 Fehlarten) separat aufsummieren und dann joinen. So müsste in deinem Beispiel eine Zeile rauskommen.

[Agasch]

Hi,
mein Bespiel zeigt nur das Wichtigste. Die Personaltabelle zeigt alle Informnationen und greift auf weitere Tabellen zu (Anschriften, Anreden usw). Das funktioniert auch alles, nur an der Stelle mit den Fehltagen gibs einen Fehler, daher reduzierte ich es auf minimum, bis ich den Fehler verstanden/behoben habe

[mkinzler]

Oder

SQL-Code:

select
    P.PERSONAL_ID,
    Sum(U.FEHLTAGE_ARBEITSTAGE) as URLAUB,
    Sum(F.FEHLTAGE_ARBEITSTAGE) as FEHLTAGE,
    Sum(K.FEHLTAGE_ARBEITSTAGE) as KRANK
from 
    PERSONAL P
        LEFT JOIN FEHLTAGE U ON U.FEHLTAGE_PERSONALID=P.PERSONAL_ID
        LEFT JOIN FEHLTAGE F ON F.FEHLTAGE_PERSONALID=P.PERSONAL_ID 
        LEFT JOIN FEHLTAGE K ON K.FEHLTAGE_PERSONALID=P.PERSONAL_ID 
where
    U.FEHLTAGE_ART='U'AND F.FEHLTAGE_ART='F' AND K.FEHLTAGE_ART='K'
GROUP BY  
    P.PERSONAL_ID;

[Agasch]

auch falsch^^

Richtig wäre:
PERSONAL_ID=1 URLAUB=17 FEHLTAGE=1 KRANK=7

Bekomme jedoch:
PERSONAL_ID=1 URLAUB=34 FEHLTAGE=4 KRANK=14

[Sir Rufo]

Ja logisch. Es hat sich seit meiner Aussage ja auch nichts Wesentliches an der Abfrage verändert. Es bleibt bei 4 Zeilen über die der GROUP BY läuft.

Versuch doch mal die Tabelle mit den Fehltagen nur einmal zu joinen und für die Urlaubstage (als Beispiel) summierst dur nur, wenn die Art = U ist.

In SQL

SQL-Code:

SUM( IF( ... ) )

.

Der Rest erfolgt analog dazu ...

[Agasch]

Das ist es... warum einfach wenns kompliziert geht^^

Also das funktioniert (IF gibs in Interbase nicht, aber case):
Der Server zeigt es richtig an. Werde schauen, wie weit ich es noch verfeinern kann (zB wenn FEHLTAGE_ARBEITSTAGE= NULL sind, event. 2 case):

select
Sum(case when FEHLTAGE_ART='U' then FEHLTAGE_ARBEITSTAGE else 0 end) as URLAUB_GENOMMEN,
Sum(case when FEHLTAGE_ART='K' then FEHLTAGE_ARBEITSTAGE else 0 end) as KRANK,
Sum(case when FEHLTAGE_ART='F' then FEHLTAGE_ARBEITSTAGE else 0 end) as FEHLTAGE,
PERSONAL_ID

from PERSONAL P
LEFT JOIN FEHLTAGE ON (FEHLTAGE_PERSONALID=PERSONAL_ID)

GROUP BY PERSONAL_ID

[jsheyer]

Hallo,

es gibt noch eine Möglichkeit, die Perfomance dabei ist natürlich im wesentlichen auch von den vorhandenen Indexen abhängig (wie fast immer ;-))

select
P.PERSONAL_ID,
(Select sum(U.FEHLTAGE_ARBEITSTAGE) from PERSONAL U where U.ID=P.ID and U.FEHLTAGE_ART='U') as Urlaub,
(Select sum(F.FEHLTAGE_ARBEITSTAGE) from PERSONAL F where F.ID=P.ID and F.FEHLTAGE_ART='F') as Fehltage,
(Select sum(K.FEHLTAGE_ARBEITSTAGE) from PERSONAL K where K.ID=P.ID and K.FEHLTAGE_ART='K') as Krank
from PERSONAL P


evtl. muss das natürlich auf eine zeitliche Beschränkung erweitert werden, wenn man z. B. nur eine Monat betrachten möchte.

[Agasch]

Hi,
InterBase unterschützt leider keine doppelten Selects^^.



Habe noch ein Problem mit meiner Abfrage:

Tabelle Perosnal hat 4 Datensätze (die Tabelle besteht natürlich aus weiteren Spalten wie Name usw.)
Id
1
2
3
4

Tabelle Urlaub 18 Datensätze, eine Abfrage mit:

select
PERSONAL_ID,
Sum(FEHLTAGE_ARBEITSTAGE) as URLAUB_GENOMMEN
from PERSONAL
LEFT JOIN FEHLTAGE ON (FEHLTAGE_PERSONALID = PERSONAL_ID)
GROUP BY
PERSONAL_ID

zeigt es richtigan:
ID URLAUB_GENOMMEN
1 25
2 14
3 6
4 22

ABER möchte ich mir die Anzahl aller Personaldatensätze anzeigen und füge COUNT dazu, geht es daneben

select
PERSONAL_ID,
count(PERSONAL_ID)as Gesamt_Personal,
Sum(FEHLTAGE_ARBEITSTAGE) as URLAUB_GENOMMEN
from PERSONAL
LEFT JOIN FEHLTAGE ON (FEHLTAGE_PERSONALID = PERSONAL_ID)
GROUP BY
PERSONAL_ID

ID Gesamt_Personal URLAUB_GENOMMEN
1 5 25
2 4 14
3 3 6
4 6 22

Er rechent glaube ich die Datensätze in der Tabelle Fehltage.
Wozu ein Count? Möchte auch gern die Gesamtzahl aller Datensätze anzeigen lassen.



Wie muss die Abfrage richtig aussehen??
Kenn wer ein Buch wo man den richtigen Weg erlernen kann, um solche Abfragen zu erstellen? Meine paar Bücher schneiden diese Sachen nur an.

Solche Beispiele wie:

select count(PERSONAL_ID)as Gesamt_Personal from PERSONAL

nutzen einem sehr wenig...

Viele Grüsse
Andreas

[Dejan Vu]

Zitat:

Zitat von Agasch (Beitrag 1257205)

ABER möchte ich mir die Anzahl aller Personaldatensätze anzeigen und füge COUNT dazu, geht es daneben
...Wozu ein Count? Möchte auch gern die Gesamtzahl aller Datensätze anzeigen lassen.

Welche 'Gesamtanzahl aller Datensätze'? Willst Du wissen, wie oft eine PersonalID in der Tabelle 'Fehltage' vorkommt? Dann zähle 'COUNT(FEHLTAGE_ID)'.

[Sir Rufo]

Es wäre schön, wenn für SQL-Code zumindest die CODE-Tags
(das sind die DELPHI-Tags)

Code:

[DELPHI]
[/DELPHI]

benutzt werden

Code:

select * 
from foo

besser noch CODE=SQL

SQL-Code:

select * 
from foo

Tabellen-Inhalte können hier sehr schön mit den TABLE-Tags präsentiert werden
(siehe dazu folgenden Beitrag http://www.delphipraxis.net/1253363-post4.html )

Der Count macht doch alles richtig, der zählt wie oft in der Selektionsmenge ein Wert für PERSONAL_ID existiert (also nicht NULL ist).
Aber hast du dir Gedanken gemacht, wie die Selektionsmenge aussieht?

SQL-Code:

select *
from PERSONAL
left join FEHLTAGE on (FEHLTAGE_PERSONALID = PERSONAL_ID)

[Blup]

Eigentlich ist die von jsheyer gepostete Abfrage im Prinzip schon optimal.

SQL-Code:

select
  P.PERSONAL_ID,
  count(P.PERSONAL_ID) as Gesamt_Personal,
  (Select sum(U.FEHLTAGE_ARBEITSTAGE) from FEHLTAGE U where (U.FEHLTAGE_PERSONALID = P.PERSONAL_ID) and (U.FEHLTAGE_ART = 'U')) as URLAUB_GENOMMEN,
from PERSONAL P

[Agasch]

Hast recht, verständlicher ist diese Variante:

PERSONAL_ID	Gesamt_Personal	URLAUB_GENOMMEN
1	5	25
2	4	14
3	3	6
4	6	22

Also, wozu Count? Ist nur als Info gedacht (ähnlich wie früher RecordCount bei BDE), um zu zeigen wie viele Datensätze nach der aktuellen Abfrage zu Verfügung stehen.
Gezählt sollen in dem Fall allerdings NUR die Datensätze aus der Personaltabelle (also wie viele Personen gibt es), also in der Spalte: Gesamt_Personal müsste überall eine 4 stehen.
würde ich aus dem Text alles was FEHLTAGE angeht entfernen, zeig er die 4 auch richtig an.
Wie Sir Rufo schon sagte, er zeigt wie oft PERSONAL_ID in Tabelle-FEHLTAGE vorkommt, was ich aber nicht haben möchte.
Beschäftige mich gerade mit Stored Proceduren, um zu sehen, was ich vom Server ausrechnen lassen kann

InterBase meldet sofort einen Fehler:
select
P.PERSONAL_ID,
count(P.PERSONAL_ID) as Gesamt_Personal,
(Select sum(U.FEHLTAGE_ARBEITSTAGE) from FEHLTAGE U where (U.FEHLTAGE_PERSONALID = P.PERSONAL_ID) and (U.FEHLTAGE_ART = 'U')) as URLAUB_GENOMMEN,
from PERSONAL P

Wie ich schon paar Mal gesagt habe, InterBase XE unterschützt leider keine doppelten Selects^^.

[Blup]

Was kommt den für ein Fehler?
Das ist ein ganz normales Inner-Select, das gabs schon bei Interbase 6.

[mkinzler]

Zitat:

Wie ich schon paar Mal gesagt habe, InterBase XE unterschützt leider keine doppelten Selects^^.

Was verstehst Du untere einem doppelten Select?

[baumina]

Ich weiß ja nicht wohin du das Ergebnis des SQL-Befehls schreibst, aber sollte es ein TDataset oder ein Nachfahre davon sein, gäbe es dafür auch TDataSet.RecordCount.

[Agasch]

zum testen nehme ich die IBConsole

Fehler:
Error at Line 1 -Attempt to execute an unprepared dynamic SQL statement

[DeddyH]

Du könntest Dir aber eine View erstellen:

SQL-Code:

SELECT
  FEHLTAGE_PERSONALID,
  FEHLTAGE_ARBEITSTAGE 
FROM
  FEHLTAGE 
WHERE
  FEHLTAGE_ART = 'U'

und dann damit joinen.

SQL-Code:

SELECT
  P.PERSONAL_ID,
  COUNT(P.PERSONAL_ID) AS Gesamt_Personal,
  SUM(U.FEHLTAGE_ARBEITSTAGE) AS URLAUB_GENOMMEN
FROM
  PERSONAL P
JOIN
  NeueView U ON U.FEHLTAGE_PERSONALID = P.PERSONAL_ID