That's one way of doing it

coentje

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

tgape

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...

Ilya_Ehrenburg

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).

coentje

@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 ;-)

rohypnol

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"?)

dtech

@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.