Jump to content

Empty result set after joins


raymond_feliciano

Recommended Posts

I am a mysql newbie and I have a query which joins 4 tables:

SELECT ci_patient_info.*, ci_study_history.study, ci_prescription_history.prescription, ci_icd9_history.icd9_code
FROM ci_patient_info
JOIN ci_study_history ON ci_patient_info.account_num = ci_study_history.account_num
JOIN ci_prescription_history ON ci_patient_info.account_num = ci_prescription_history.account_num
JOIN ci_icd9_history ON ci_patient_info.account_num = ci_icd9_history.account_num
WHERE ci_patient_info.account_num='2'

This works as expected as long as all the fields being selected are not empty but, if any of these fields are empty I get an empty result set. I thought I would still display fields that are not empty. Is my query wrong because I don't understand what is going on. Any help is appreciate and thanks in advanced.

Link to comment
Share on other sites

A regular (INNER) JOIN requires that both tables have matching values. An OUTER JOIN does not.

 

SELECT...
FROM...
LEFT OUTER JOIN...
LEFT OUTER JOIN...
LEFT OUTER JOIN...
WHERE...

Note that the "OUTER" is optional as long as you use a LEFT or RIGHT.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.