Retrieve data from 3 tables - MySQL

Question

Hi I have three tables.the table commoninfo contains nameDOBEducationSpeciality fields.Thenthe table contactinfo contains mobilenocommonid fields.Here 'commonid' is the foreign key from commoninfo table.Thenthe table department contains departmentcommonid fields.Here 'commomid' is the foreign key fromcommoninfo table.All data's in 'commominfo' table also available in 'department' table.But all the data in 'commominfo' table not available in 'contactinfo' table.Some of the data are missing in 'contactinfo' table.Now I want to get the records from these 3 tables based on 'commonid' field.i use the following: CREATE DEFINER=`root`@`%` PROCEDURE `SP_EditDoct`(in DID int) BEGIN SELECT DoctInfo.firstnameDoctInfo.middleinitialDoctInfo.lastname Date_Format(DoctInfo.dateofbirth'%m%d%y')DoctInfo.educationDoctInfo.Speciality DoctInfo.genderContInfo.value as MobileNoDept.Deptid FROM commonpersoninfo DoctInfocontactinfo ContInfodepartmentview Dept LEFT JOIN contactinfo ON DoctInfo.pkey=ContInfo.whosInfo_pkey WHERE DoctInfo.pkey = DID AND DoctInfo.pkey=Dept.CommonPersonInfo_Pkey AND DoctInfo.pkey=ContInfo.whosInfo_pkey; END But the problem here isit retrieves which data is available in 3 tables.I want to generate dataif it available in 'commominfo' table only. Can i use left join her?how i use it here?Can anyone help me to do this?

Iftikar Hussain · Answer

Hi try this. SELECT DoctInfo.firstname DoctInfo.middleinitial DoctInfo.lastname Date_Format(DoctInfo.dateofbirth'%m%d%y') DoctInfo.educationDoctInfo.Speciality DoctInfo.genderContInfo.value as MobileNo Dept.Deptid FROM commonpersoninfo DoctInfo LEFT JOIN contactinfo ContInfo ON DoctInfo.commonid=ContInfo.commonid INNER JOIN departmentview Dept ON DoctInfo.commonid=Dept.commonid Regards Iftikar

Kavi suja · Answer

Hi IftikarThank you very much.Thanks a lot.It's working

Kavi suja · Answer

HiThere's no changes in result :( Is there any idea other than using Left Join for retriving data?

Sanjeeb Lenka · Answer

try this it may help you SELECT DoctInfo.firstnameDoctInfo.middleinitialDoctInfo.lastname Date_Format(DoctInfo.dateofbirth'%m%d%y')DoctInfo.educationDoctInfo.Speciality DoctInfo.genderContInfo.value as MobileNoDept.Deptid FROM contactinfo ContInfo left JOIN commonpersoninfo DoctInfo ON DoctInfo.pkey=ContInfo.whosInfo_pkey left join departmentview Dept on Dept.pkey=ContInfo.whosInfo_pkey WHERE DoctInfo.pkey = DID AND DoctInfo.pkey=Dept.CommonPersonInfo_Pkey AND DoctInfo.pkey=ContInfo.whosInfo_pkey;

Kavi suja · Answer

Hi Ravi Thank you for your reply.Actually this returns the same result as previous one.For example 'commoninfo' contains data for PID=55'department' contains data for PID=55But 'contactinfo' doesn't contain data for PID=55.Sowhen I give 55 for PIDit'll return no records.But I want the data available in 'commoninfo' and 'department' tables.How I return this?

Ravi Shekhar · Answer

Try This SELECT DoctInfo.firstnameDoctInfo.middleinitialDoctInfo.lastname Date_Format(DoctInfo.dateofbirth'%m%d%y')DoctInfo.educationDoctInfo.Speciality DoctInfo.genderContInfo.value as MobileNoDept.Deptid FROM contactinfo ContInfo LEFT JOIN commonpersoninfo DoctInfo ON DoctInfo.pkey=ContInfo.whosInfo_pkey departmentview Dept WHERE DoctInfo.pkey = DID AND DoctInfo.pkey=Dept.CommonPersonInfo_Pkey AND DoctInfo.pkey=ContInfo.whosInfo_pkey;