MySql - Data From 3 tables -Left Joins

Question

Hi I have to retrieve data from 3 tables usind common id.I include the SP following.Here i have whosinfo table.This tables contains 2 fieldspkey and hisPkey.It stores the common id as hispkey.In this procedure i've to check continfo.whosinfo_pkey=whos.pkey for mobile number taken from contactinfo table.I want to use subquery here.But there are some errors.I dont know how to apply sub query.Can anyone helps me to solve this? CREATE DEFINER=`root`@`%` PROCEDURE `SP_EditDoct`(in DID int) BEGIN SELECT Dept.Deptid DoctInfo.SalutationList_pkey DoctInfo.firstname DoctInfo.middleinitial DoctInfo.lastname DATE_FORMAT(DoctInfo.dateofbirth'%m/%d/%Y') DoctInfo.educationDoctInfo.Speciality DoctInfo.genderContInfo.value as MobileNo FROM commonpersoninfo DoctInfo LEFT JOIN contactinfo ContInfo ON DoctInfo.pkey=ContInfo.whosInfo_pkey INNER JOIN departmentview Dept ON DoctInfo.pkey=Dept.CommonPersonInfo_Pkey /* INNER JOIN whosinfo Whos ON ContInfo.whosInfo_pkey = Whos.hisPkey*/ WHERE DoctInfo.pkey=DID; END

Iftikar Hussain · Answer

it should work please check your data in commonpersoninfo and departmentview select A.pkey A.firstname B.deptid D.mobileno FROM @commonpersoninfo A INNER JOIN @departmentview B ON B.commonpersoninfo_pkey=A.pkey LEFT JOIN @whosinfo C ON C.hisPkey=A.pkey LEFT JOIN @contactinfo D ON D.whosinfo_pkey=C.wkey WHERE A.pkey=365 Regards Iftikar

Kavi suja · Answer

Hi Iftikar Thank you very much.It's working....

Kavi suja · Answer

Hi I try this.But the data not retrived from commonpersoninfodepartmentview.(I want other data with 'mobileno' field as null)

Iftikar Hussain · Answer

if the data also not available in whosinfo you can do like this select A.salutationlist_pkey A.firstname A.middleinitial A.lastname A.dateofbirth A.education A.speciality A.gender B.deptid D.mobileno FROM commonpersoninfo A INNER JOIN departmentview B ON B.commonpersoninfo_pkey=A.pkey LEFT JOIN whosinfo C ON C.hisPkey=A.pkey LEFT JOIN contactinfo D ON D.whosinfo_pkey=C.wkey Regards Iftikar

Kavi suja · Answer

Hi I alrady check in this way for did = 365.But the data not retrieved.Actually commonpersoninfo  departmentview have data for did=365.The tables whosinfo contactinfo are not contains the data for did=365.So when i run following query no data returned.But i want to return all the data with mobileno as Null value. A.salutationlist_pkey A.firstname A.middleinitial A.lastname A.dateofbirth A.education A.speciality A.gender B.deptid D.mobileno FROM commonpersoninfo A INNER JOIN departmentview B ON B.commonpersoninfo_pkey=A.pkey INNER JOIN whosinfo C ON C.hisPkey=A.pkey LEFT JOIN contactinfo D ON D.whosinfo_pkey=C.wkey WHERE A.pkey=DID;

Iftikar Hussain · Answer

try like this select A.salutationlist_pkey A.firstname A.middleinitial A.lastname A.dateofbirth A.education A.speciality A.gender B.deptid D.mobileno FROM commonpersoninfo A INNER JOIN departmentview B ON B.commonpersoninfo_pkey=A.pkey INNER JOIN whosinfo C ON C.hisPkey=A.pkey LEFT JOIN contactinfo D ON D.whosinfo_pkey=C.wkey Regards Iftikar

Kavi suja · Answer

Hi Iftikar Thank you for your reply.Now it's working for me.But i've another issue here.Sometimes the table 'contactinfo' doesn't have data(ie. mobileno) for particular whosinfo_pkey or 'whosinfo' doesn't have data for particular hisPkey.That timeI want to get another data except mobileno.Using your last query I can include the data with mobile number.But whenever execute query for the data not available in 'contactinfo''whosinfo' I want to generate other fields with mobilenumber as null.

Iftikar Hussain · Answer

Hi Try like this select A.salutationlist_pkey A.firstname A.middleinitial A.lastname A.dateofbirth A.education A.speciality A.gender B.deptid D.mobileno FROM commonpersoninfo A INNER JOIN departmentview B ON B.commonpersoninfo_pkey=A.pkey INNER JOIN whosinfo C ON C.hisPkey=A.pkey INNER JOIN contactinfo D ON D.whosinfo_pkey=C.wkey Regards Iftikar

Kavi suja · Answer

Hi Iftikar  Table - commonpersoninfo--- Fields : pkeysalutationlist_pkeyfirstnamemiddleinitiallastnamedateofbirtheducationspecialitygender. Table - departmentview--- Fields : commonpersoninfo_pkeydeptid. Table - contactinfo--- Fields : mobilenowhosinfo_pkey. Table - whosinfo--- Fields : wkeyhisPkey I want to get salutationlist_pkeyfirstnamemiddleinitiallastnamedateofbirtheducationspecialitygendermobileno deptid based on particular key.This key is pkey in 'commonpersoninfo'commonpersoninfo_pkey in 'departmentview'hisPkey in 'whosinfo'.In above procedure all data are retrieved expect mobileno from contactinfo table. sample Data: commonpersoninfo-345'Mr''Nirmal''Paul''J''1986-01-06''MBBS''OHS''Male'. departmentview-345'3'. contactinfo- '9600765342'342 whosinfo- 342345

Pankaj Pandey · Answer

try this within your SP.. SELECT Dept.Deptid DoctInfo.SalutationList_pkey DoctInfo.firstname DoctInfo.middleinitial DoctInfo.lastname DATE_FORMAT(DoctInfo.dateofbirth'%m/%d/%Y') DoctInfo.educationDoctInfo.Speciality DoctInfo.genderContInfo.value as 'MobileNo' FROM commonpersoninfo DoctInfo LEFT JOIN contactinfo ContInfo ON DoctInfo.pkey=ContInfo.whosInfo_pkey LEFT JOIN departmentview Dept ON DoctInfo.pkey=Dept.CommonPersonInfo_Pkey WHERE DoctInfo.pkey=DID;

Iftikar Hussain · Answer

Hi Could provide example for your 3 table data? How exactly you want the output? Regards Iftikar