MySql - Data From 3 tables -Left Joins

Kavi suja

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 fields,pkey 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.education,DoctInfo.Speciality,

DoctInfo.gender,ContInfo.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

11 Replies

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

Iftikar Hussain13y ago
Accepted 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 suja13y ago
Hi Iftikar,
Thank you very much.It's working....
Kavi suja13y ago
Hi,
I try this.But the data not retrived from commonpersoninfo,departmentview.(I want other data with 'mobileno' field as null)
Iftikar Hussain13y ago
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 suja13y ago
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 Hussain13y ago
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 suja13y ago
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 time,I 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 Hussain13y ago
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 suja13y ago
Hi Iftikar,
Table - commonpersoninfo--- Fields : pkey,salutationlist_pkey,firstname,middleinitial,lastname,dateofbirth,education,speciality,gender.
Table - departmentview--- Fields : commonpersoninfo_pkey,deptid.
Table - contactinfo--- Fields : mobileno,whosinfo_pkey.
Table - whosinfo--- Fields : wkey,hisPkey

I want to get salutationlist_pkey,firstname,middleinitial,lastname,dateofbirth,education,speciality,gender,mobileno,
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- 342,345
Pankaj Pandey13y ago
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.education,DoctInfo.Speciality,
DoctInfo.gender,ContInfo.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 Hussain13y ago
Hi,
Could provide example for your 3 table data? How exactly you want the output?

Regards,
Iftikar