raymond_feliciano Posted June 11, 2012 Share Posted June 11, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/264024-empty-result-set-after-joins/ Share on other sites More sharing options...
requinix Posted June 12, 2012 Share Posted June 12, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/264024-empty-result-set-after-joins/#findComment-1353053 Share on other sites More sharing options...
raymond_feliciano Posted June 12, 2012 Author Share Posted June 12, 2012 Thanks works great. Quote Link to comment https://forums.phpfreaks.com/topic/264024-empty-result-set-after-joins/#findComment-1353140 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.