Getting the personal contact details of an employee

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.

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,