I want to get the Email, Phone and Mobile number of each employee.
The ContactDetailsType contains the Label which is email,phone,fax… and ContactDetails contains the ContactDetailsTypeId (the ID of ContactDetailsType) and the Info having the value of the phone number (for example).
SELECT Concat(Concat(E.[firstname], ' '), E.[lastname]) AS full_name, (SELECT cc.info FROM [contactdetails] cc LEFT JOIN [contactdetailstype] cd ON cd.contactdetailstypeid = cc.contactdetailstypeid WHERE cd.label = 'Phone') AS phone, CDC.label, CDT.label, CD.info FROM [employee] [E] LEFT JOIN [af_adminfile] [AFA] ON E.adminfileid = AFA.adminfileid LEFT JOIN [af_contactdetails] [AFC] ON AFC.adminfileid = AFA.adminfileid LEFT JOIN [contactdetails] [CD] ON CD.contactdetailsid = AFC.contactdetailsid LEFT JOIN [contactdetailscategory] [CDC] ON CDC.contactdetailscateroryid = CD.contactdetailscategoryid LEFT JOIN [contactdetailstype] [CDT] ON CDT.contactdetailstypeid = CD.contactdetailstypeid
I tried the following query to get the phone but I’m getting the following error :
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Advertisement
Answer
Right now your subquery is bringing all contact details, you need a correlated query so is filter by the outside query. also be carefull because you are using the same alias inside and out.
This may work, but again without data structure is just a guess. Hope at least give you the idea of what you should do.
(SELECT cc.info FROM [contactdetails] cc LEFT JOIN [contactdetailstype] cdt2 ON cdt2.contactdetailstypeid = cc.contactdetailstypeid WHERE cdt2.label = 'Phone' AND cc.contactdetailsid = cd.contactdetailsid -- the id from outside query ) AS phone,