MySQL returned an empty result set

[jsbert]

Hi,

I am trying to retrieve info from 5 different tables. The search criteria is; look for the lastest date in a table call dateIndex which relates to a pacients id number.
For that, of course I use the patient´s id number and the MAX() Function to find the latest date related to him.
That works fine. I put the date in a variable called $mostRecent and use it in the query.

In the example I'm providing here, the var is substituted by an actual date, because I know that on that date the pacient 1 had three tests on 2009-05-19 (this is fictutious by the way), namely blood tests (hematologia, bioquimica and serologia). No urin or feces exams were conducted on that date.

When I use the following query, I get the info I expected to get:

SELECT hematologia.*, bioquimica.*, serologia.*
FROM paciente
LEFT JOIN lab.bioquimica ON paciente.Id = bioquimica.id
LEFT JOIN lab.hematologia ON paciente.Id = hematologia.id
LEFT JOIN lab.serologia ON paciente.Id = serologia.id
WHERE ((paciente.Id =1) AND (hematologia.date ='2009-05-19') AND (bioquimica.date ='2009-05-19') AND (serologia.date ='2009-05-19'))

But since the user of the program won't know what was done to the patient, I need to have a global query, but I get an empty page - I guess it's empty because it couldn't find any results in the urin and feces table. What can I do about not getting an empty results page? Any ideas? The global query I used is the following:

SELECT hematologia.*, bioquimica.*, serologia.*, orina.*, heces.*
FROM paciente
LEFT JOIN lab.bioquimica ON paciente.Id = bioquimica.id
LEFT JOIN lab.hematologia ON paciente.Id = hematologia.id
LEFT JOIN lab.serologia ON paciente.Id = serologia.id
LEFT JOIN lab.orina ON paciente.Id = orina.id
LEFT JOIN lab.heces ON paciente.Id = heces.id
WHERE ((paciente.Id =1) AND (hematologia.date ='2009-05-19') AND (bioquimica.date ='2009-05-19') AND (serologia.date ='2009-05-19')
AND (orina.date ='2009-05-19') AND (heces.date ='2009-05-19'))

Any ideas on how to solve this will be very much appreciated. I am close to the dead line, and this is the final part of my project.

thanks a lot in advance

[bubak]

When using a left join, all the conditions have to be entered within the left join or it will act like a regular join.

For example:
LEFT JOIN table2 ON table2.id=table1.id
WHERE table2.color='blue'

is the same as
JOIN table2 ON table2.id=table1.id
WHERE table2.color='blue'

in order to get the left join effect the code should look something like:
LEFT JOIN table2 ON table2.id=table1.id AND table2.color='blue'

So to answer your question, you are currently joining, and not really left joining. I think this would work:


SELECT hematologia.*, bioquimica.*, serologia.*, orina.*, heces.*
FROM paciente
LEFT JOIN lab.bioquimica ON paciente.Id = bioquimica.id AND bioquimica.date ='2009-05-19'
LEFT JOIN lab.hematologia ON paciente.Id = hematologia.id AND hematologia.date ='2009-05-19'
LEFT JOIN lab.serologia ON paciente.Id = serologia.id AND serologia.date ='2009-05-19'
LEFT JOIN lab.orina ON paciente.Id = orina.id AND orina.date ='2009-05-19'
LEFT JOIN lab.heces ON paciente.Id = heces.id AND heces.date ='2009-05-19'
WHERE (paciente.Id =1)

[jsbert]

Hi, Bubak

I tried what you suggested and it worked like a charm. Thanks, a million.
Very good explanation on top of that.
If it's alright with you I have another question.

Talk to you soon