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