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. 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. 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. 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
Archived
This topic is now archived and is closed to further replies.