[SQL JOIN DB2] problemen met een JOIN van 5 tabellen

[Bladerer]

Hallo allemaal,

Ik heb problemen met de bouw van een query waarin ik een selectie wil maken van 5 afzonderlijke tabellen. Het gaat om een tijdschrijfsysteem waarbij er 5 tabellen zijn:

Tabel 1 : Project gegevens
Tabel 2 : Taak gegvens (De taken die bij een project horen)
Tabel 3 : Medewerkers (Alle medewerkers die het systeem gebruiken)
Tabel 4 : Taak/Medewerkers (koppel tabel Taak en Medewerkers)
Tabel 5 : Uren (De uren per taak per datum voor een medewerker)



De DB2 /400 ondersteund geen FULL OUTER JOIN vandaar dat ik de volgende query heb gebouwd:

code:

SELECT   AUT.TAAK_ID, AUT.TAAK_OM, AUT.TAAK_ST, AUT1.MEDEWERKER_ID, 
         AUT1.MEDEWERKER_NM, AUT1.MEDEWERKER_CD, 
         (SUM(HOUR(AUT2.TIJD_TI)) + SUM(MINUTE(AUT2.TIJD_TI))/60) AS TIJD, AUT4.PROJECT_ID, AUT4.PROJECT_OM 
FROM     OURTOOLS.AUTPJA04 AUT3
         LEFT OUTER JOIN OURTOOLS.AUTPJA03 AUT2 ON AUT3.MEDEWERKER_ID=AUT2.MEDEWERKER_ID AND AUT3.PROJECT_ID=AUT2.PROJECT_ID AND AUT3.TAAK_ID=AUT2.TAAK_ID
         LEFT OUTER JOIN OURTOOLS.AUTPJA02 AUT1 ON AUT3.MEDEWERKER_ID=AUT1.MEDEWERKER_ID
         LEFT OUTER JOIN OURTOOLS.AUTPJA01 AUT ON AUT3.TAAK_ID=AUT.TAAK_ID, 
         OURTOOLS.AUTPJA00 AUT4 
WHERE    AUT.PROJECT_ID=AUT4.PROJECT_ID 
AND      AUT1.MEDEWERKER_ID = ? 
GROUP BY AUT.TAAK_ID, AUT.TAAK_OM, AUT.TAAK_ST, AUT1.MEDEWERKER_ID, AUT1.MEDEWERKER_NM, AUT1.MEDEWERKER_CD, AUT4.PROJECT_ID, AUT4.PROJECT_OM 
ORDER BY 8, 1

Deze query werkt wel, althans zo dachten we.
We krijgen echter onverwachtse resultaten.
Zo komt het namelijk bij idere medewerker opeens voor dat ze projecten zien die ze nog nooit eerder zagen met daarbij onbekende taken en uren.
Ook komt het voor dat bij wel correcte projecten onbekende taken komen te staan met ongeboekte uren.

Komt het voor, dan zien de mensen meestal dat er op die taken 8 uur is geboekt en soms staat er 42 uur.

De medewerker ID wordt dmv eeb prepared statement later toegevoegd.
Ik ben al dagen bezig met de bouw van dit systeem en zie door de bomen het bos niet meer. Wie kan mij helpen want ik heb het hele internet al doorzocht.

[Robbemans]

Het datamodel lijkt me nogal redundant... Klopt dit wel?

Ik neem iig aan dat je voor Uren geen MEDEWERKER_ID en PROJECT_ID nodig hebt. Die zijn toch via een JOIN op de halen adhv de Taak/Medewerkers?

Daarbij denk ik dat je ook (als je het zo wilt doen als je query eruit ziet) je de laatste join moet uitbreiden met AUT3.PROJECT_ID = AUT.PROJECT_ID

[ Voor 86% gewijzigd door Robbemans op 03-08-2004 15:10 ]

[akakiwi]

Dat je taken ziet die een werknemer niet toegekent heeft gekregen ligt aan je LEFT OUTER JOIN.
Je zult waarschijnlijk zien dat elke werknemer alle taken en projecten ziet, met daarbij alle uren die door alle andere medewerkers geboekt zijn op die taken binnen een project.
Ik raad je aan een INNER JOIN te gebruiken. Dan weet je zeker dat je alleen dat per medewerker te zien krijgt wat hij/zij mag zien, omdat dat zijn/haar geboekte uren zijn.

[Robbemans]

akakiwi schreef op 03 augustus 2004 @ 15:14:
Ik raad je aan een INNER JOIN te gebruiken. Dan weet je zeker dat je alleen dat per medewerker te zien krijgt wat hij/zij mag zien, omdat dat zijn/haar geboekte uren zijn.

Enig nadeel is dat je dan niet ALLE medewerkers ziet als je dat perse wil.

Wellicht is het slim om vanuit AUT1 te beginnen (FROM) omdat je dan alle medewerkers 1 keer hebt. Dan kun je de uren er via een subselect bijhalen, of via boolean aggregates (als DB2 dit kan iig)

[akakiwi]

Als je per se alle medewerkers zou willen zien, zou ik het oplossen met een UNION.
Dan haal je eerst alle medewerkers eruit die uren geboekt hebben, waarna je onder de UNION alle medewerkers ophaalt die nog geen uren geboekt hebben.

[Bladerer]

Het model is idd behoorlijk dubbelop.
Daar kan ik helaas niets aan veranderen.
Deze is ooit gebouwd door een collega en momenteel al zolang in productie dat het onmogelijk is dit nog aan te passen.

Het probleem dat zich voor deed is nu opgelost de code is nu als volgt geworden:

code:

SELECT   AUT.TAAK_ID, AUT.TAAK_OM, AUT.TAAK_ST, AUT1.MEDEWERKER_ID, 
         AUT1.MEDEWERKER_NM, AUT1.MEDEWERKER_CD, 
         (SUM(HOUR(AUT2.TIJD_TI)) + SUM(MINUTE(AUT2.TIJD_TI))/60) AS TIJD, AUT4.PROJECT_ID, AUT4.PROJECT_OM 
FROM     OURTOOLS.AUTPJA04 AUT3
         LEFT OUTER JOIN OURTOOLS.AUTPJA03 AUT2 ON AUT3.MEDEWERKER_ID=AUT2.MEDEWERKER_ID AND AUT3.PROJECT_ID=AUT2.PROJECT_ID AND AUT3.TAAK_ID=AUT2.TAAK_ID
         LEFT OUTER JOIN OURTOOLS.AUTPJA02 AUT1 ON AUT3.MEDEWERKER_ID=AUT1.MEDEWERKER_ID
         LEFT OUTER JOIN OURTOOLS.AUTPJA01 AUT ON AUT3.TAAK_ID=AUT.TAAK_ID  AND AUT3.PROJECT_ID=AUT.PROJECT_ID,
         OURTOOLS.AUTPJA00 AUT4 
WHERE    AUT.PROJECT_ID=AUT4.PROJECT_ID 
AND      AUT1.MEDEWERKER_ID = 'EWB' 
GROUP BY AUT.TAAK_ID, AUT.TAAK_OM, AUT.TAAK_ST, AUT1.MEDEWERKER_ID, AUT1.MEDEWERKER_NM, AUT1.MEDEWERKER_CD, AUT4.PROJECT_ID, AUT4.PROJECT_OM 
ORDER BY 8, 1

Zoals jullie mogelijk zien is er een extra ON koppeling aangebracht (AND AUT3.PROJECT_ID=AUT.PROJECT_ID) hierdoor lijkt het probleem voorkomen.
Bedankt voor de hulp.

[Robbemans]

Is ook chic, alleen nog steeds 2 queries ipv 1 met boolean aggregates.

@Bladerer: als je niet bekend bent met boolean aggregates, dan is dit een handige link:
http://www.shadowcentral.net/2003/08/the_rozenshtein.html

Ik zie dat je de oplossing idd hebt zoals ik hem al aandroeg, met de extra controle op project id

[ Voor 18% gewijzigd door Robbemans op 03-08-2004 15:43 ]

[Bladerer]

IDD, mijn dank is groot

[Bladerer]

Ik ben nu aangeland bij het volgende probleem.
Ik wil de query nog iets verder uitbouwen en wel in de volgende zin:

er moet een selectie gemaakt worden van alle projecten en daarbij behorende taken voor een bepaald persoon. I.p.v. het optellen van het aantal uren dat een persoon aan een taak werkte wil ik nu een selectie maken van 1 dag.

Heeft een persoon die dag tijd geschreven op een taak, laat dan het aantal uren zien voor die taak, voor die persoon voor een bepaalde dag.

Heeft een persoon die dag nog niet geschreven op die taak op die betreffende dag, laat dan een null waarde zien.

BV:

project1 | project1omschrijving | taak1 | taak1omschrijving | MedewerkerID |10
project2 | project1omschrijving | taak1 | taak1omschrijving | MedewerkerID |20
project3 | project1omschrijving | taak1 | taak1omschrijving | MedewerkerID |NULL
project3 | project1omschrijving | taak2 | taak1omschrijving | MedewerkerID |2

Momenteel heb ik het probleem dat wanneer ik selectie criterium van de datum toevoeg op de uren tabel ik alleen taken en projecten zie waarop die dag al is geschreven. Heeft de persoon nog nooit uren op die datum geschreven dan zie ik totaal geen gegevens van die persoon.

FULL OUTER JOIN lijkt mij de oplossing, alleen dit wordt niet ondersteund
EXCEPTION JOIN wel, maar ik snap bij een dergelijke query tussen 5 tabellen niet hoe ik dat het beste kan doen.

Iemand een suggestie?

[Robbemans]

Full Outer Join, is dat hetzelfde als een LEFT OUTER JOIN?

Hier gebruikt je normaliter een LEFT OUTER JOIN voor, zodat de join niet de NULL waarden eruit filtert. Je kunt eventueel de query samenvoegen door de velden in een subquery (PER VELD!) erbij te halen.