That's one way of doing it



  • But I would've preferred a straight inner join

                      SELECT    tblComplicatieAard.strComplicatieAard, COUNT(tblLAR.lngComplicatie2ID) AS Aantal
                      FROM      tblLAR
                                INNER JOIN tblComplicatieAard ON tblLAR.lngComplicatie2ID = tblComplicatieAard.lngComplicatieAardID
                                RIGHT OUTER JOIN tblOnderzoek
                                RIGHT OUTER JOIN tblPatientStatus ON tblOnderzoek.lngPatientID = tblPatientStatus.lngPatientID
                                                                     AND tblOnderzoek.lngStatusID = tblPatientStatus.lngStatusPatient ON tblLAR.lngPatientID = tblPatientStatus.lngPatientID
                                                                                                                                         AND tblLAR.lngStatusID = tblPatientStatus.lngStatusPatient
                                RIGHT OUTER JOIN tblPatient ON tblPatientStatus.lngPatientID = tblPatient.lngPatientID
                      WHERE     NOT tblOnderzoek.lngBehandelingID IS NULL
                                AND NOT tblPatient.bitAWBZ IS NULL
                                AND NOT tblLAR.lngGraviditeit IS NULL
                                AND NOT tblLAR.lngLARID IS NULL
                                AND NOT tblOnderzoek.lngOnderzoekID IS NULL
    


  • My SQL is a bit rusty.  However, it appears to me that this query at least allows for tblOnderzoek.IngPatientID, tblOnderzoek.IngStatusID, and tblPatient.IngPatientID to be NULL, but a straight INNER JOIN would not.  It may even allow for tblPatientStatus.IngPatientID and tblPatientStatus.IngStatusPatient to be NULL.  That having been said, it is a big, nasty-looking query that could probably be cleaned up a bit and might even perform better as a result - especially if there is no desire to include elements where the above listed fields are null.  Also, I'd personally think the patient ID fields at least should never be NULL, but that could just be me.

    Are the field and table names at least partially in a language other than English?  Or am I just confused?  I'm probably just confused.  That could be why I keep thinking the field names are all homages to Mr. Tulip... <!-- from Terry Pratchett's Truth -->



  •  The names are dutch (or afrikaans). I have problems with
    ComplicatieAard. I'd love to translate it as "complication earth". That
    makes no sense, however, so it's probably "sort of complication", as
    "aard" could be the same word as "Art" (german).



  • @Ilya Ehrenburg said:

     The names are dutch (or afrikaans). I have problems with
    ComplicatieAard. I'd love to translate it as "complication earth". That
    makes no sense, however, so it's probably "sort of complication", as
    "aard" could be the same word as "Art" (german).

    It is in Dutch, "Complicatie Aard" means "Type of complication" well more or less anyway ;-)



  •  Haha! Are you kidding me? I'd post one of the MySQL queries I've had to DEBUG but I might get sued if I do that (it's company property). So, here's the thing: we're talking about 10 tables, 15 unions, countless inner/left joins, countless conditions, countless calls to functions such as TIME_TO_SEC, SEC_TO_TIME, DATE_ADD, GROUP BY, IF, IFNULL or GROUP_CONCAT, tons of extremely simple aliases such as F, FP, F2, F, F, R, O, RF, OF, A, AF, C, P, GC, all in about 800 lines that are around 40 characters each, written as a string in PHP in unformatted/unindented MySQL code that never heard of views. The result of that query is then further processed in PHP and the final result (an array) is returned by the function. The result of the function is further processed in PHP and the restul of that is passed to Smarty which does some processing (loops and conditonals) when displaying the result. That was the worst thing I've ever had to debug and it took me nearly two weeks just to understand what was going on around there, one week to find the error and a few more days to fix it and a few more days to fix the bugs I've introduced and a few more days to fix the bugs I've introduced while fixing the bugs I've introduced. It was simply a nightmare and I mean a nightmare... I've literally had nightmares in which I've seen myself working on that query! The whole database and all the queries were a complete disaster waiting to happen (and sometime it did happen!)and every time they complained that the system was running slow again, we'd just add another index and pray for them not to notice that, even if the reports are faster, the inserts/updates are slower.

    I will do my best to send Alex some bits of that query tomorrow and hopefully they won't reach the front page (can you spell "breaking NDAs"?)



  • @coentje said:

    @Ilya Ehrenburg said:

     The names are dutch (or afrikaans). I have problems with
    ComplicatieAard. I'd love to translate it as "complication earth". That
    makes no sense, however, so it's probably "sort of complication", as
    "aard" could be the same word as "Art" (german).

    It is in Dutch, "Complicatie Aard" means "Type of complication" well more or less anyway ;-)

     

    a 100% correct translation would be "Complication nature" or "Nature of complication". "Type of complication" would just be "ComplicatieType". It pretty much means the same anyway.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.