Retrieve data from 3 tables - MySQL

Kavi suja

Hi,
I have three tables.the table "commoninfo" contains "name,DOB,Education,Speciality" fields.Then,the table "contactinfo" contains "mobileno,commonid" fields.Here 'commonid' is the foreign key from "commoninfo" table.Then,the table "department" contains "department,commonid" fields.Here 'commomid' is the foreign key from"commoninfo" 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.firstname,DoctInfo.middleinitial,DoctInfo.lastname,

Date_Format(DoctInfo.dateofbirth,'%m%d%y'),DoctInfo.education,DoctInfo.Speciality,

DoctInfo.gender,ContInfo.value as MobileNo,Dept.Deptid

FROM

commonpersoninfo DoctInfo,contactinfo ContInfo,departmentview 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 is,it retrieves which data is available in 3 tables.I want to generate data,if it available in 'commominfo' table only. Can i use left join her?how i use it here?Can anyone help me to do this?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
Hi,
try this.

SELECT
DoctInfo.firstname,
DoctInfo.middleinitial,
DoctInfo.lastname,
Date_Format(DoctInfo.dateofbirth,'%m%d%y'),
DoctInfo.education,DoctInfo.Speciality,
DoctInfo.gender,ContInfo.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
0
Kavi suja13y ago
Hi Iftikar,Thank you very much.Thanks a lot.It's working
0
Kavi suja13y ago
Hi,There's no changes in result :( Is there any idea other than using Left Join for retriving data?
0
Sanjeeb Lenka13y ago
try this it may help you,

SELECT
DoctInfo.firstname,DoctInfo.middleinitial,DoctInfo.lastname,
Date_Format(DoctInfo.dateofbirth,'%m%d%y'),DoctInfo.education,DoctInfo.Speciality,
DoctInfo.gender,ContInfo.value as MobileNo,Dept.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;
0
Kavi suja13y ago
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=55,But 'contactinfo' doesn't contain data for PID=55.So,when I give 55 for PID,it'll return no records.But I want the data available in 'commoninfo' and 'department' tables.How I return this?
0
Ravi Shekhar13y ago

Try This

SELECT DoctInfo.firstname,DoctInfo.middleinitial,DoctInfo.lastname,
Date_Format(DoctInfo.dateofbirth,'%m%d%y'),DoctInfo.education,DoctInfo.Speciality,
DoctInfo.gender,ContInfo.value as MobileNo,Dept.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;
0