How to add an additional field to an existing query

1

\\SELECT POSDocument.DocumentID, POSDocument.POSDocument_ID, POSDocument.DocumentType, POSDocument.InvoiceNumber, POSDocumentDetail.POSDocumentDetail_ID, POSDocumentDetail.POSDocument_ID AS POSDocument_ID2,
POSDocumentDetail.LineType, POSDocumentDetail.ItemNumber, POSDocumentDetail.Description, POSDocumentDetail.ItemID, POSDocument.CustomerName, POSDocument.CustomerAddress1, POSDocument.CustomerCity, POSDocument.CustomerState, POSDocument.CustomerZip,POSDocument.Customer_ID, POSDocumentDetail.isVoid,
CM.Party_ID, CM.Electronic_Address, CM.IsPrimary, CM.ContactMechanismType, CM.ContactMechanismPurpose
FROM POSDocument INNER JOIN
POSDocumentDetail ON POSDocument.POSDocument_ID = POSDocumentDetail.POSDocument_ID OUTER APPLY (
SELECT TOP (1) *
FROM ContactMechanism CM
WHERE CM.Party_ID = POSDocument.Customer_ID AND
ContactMechanismType = 'Email' AND ContactMechanismPurpose = 'Business' ) AS CM
WHERE (POSDocumentDetail.LineType = 5) AND (POSDocument.DocumentType = 4) AND (POSDocumentDetail.isVoid = 0)
ORDER BY POSDocument.Customer_ID\\

This works perfectly but I need to add another field to this but not sure how to do it. I need to add a phone number just like the email as shown below:
WHERE CM.Party_ID = POSDocument.Customer_ID AND
ContactMechanismType = 'Email' AND ContactMechanismPurpose = 'Business' )

The phone number is should be something like:
WHERE CM.Party_ID = POSDocument.Customer_ID AND
ContactMechanismType = 'Phone' AND ContactMechanismPurpose = 'Business' )

The field I need to display is:
Contact_Mechanism_ID.Phone_Number

2

You should be able to just add another OUTER APPLY with the changes for the where clause. If we format the code - it is much easier to see:

Select POSDocument.DocumentID
     , POSDocument.POSDocument_ID
     , POSDocument.DocumentType
     , POSDocument.InvoiceNumber
     , POSDocumentDetail.POSDocumentDetail_ID
     , POSDocumentDetail.POSDocument_ID As POSDocument_ID2
     , POSDocumentDetail.LineType
     , POSDocumentDetail.ItemNumber
     , POSDocumentDetail.Description
     , POSDocumentDetail.ItemID
     , POSDocument.CustomerName
     , POSDocument.CustomerAddress1
     , POSDocument.CustomerCity
     , POSDocument.CustomerState
     , POSDocument.CustomerZip
     , POSDocument.Customer_ID
     , POSDocumentDetail.isVoid
     , CM.Party_ID
     , CM.Electronic_Address
     , CM.IsPrimary
     , CM.ContactMechanismType
     , EM.ContactMechanismPurpose
     , EM.Party_ID
     , EM.Electronic_Address
     , EM.IsPrimary
     , EM.ContactMechanismType
     , EM.ContactMechanismPurpose
  From POSDocument
 Inner Join POSDocumentDetail On POSDocument.POSDocument_ID = POSDocumentDetail.POSDocument_ID
 Outer APPLY
  (
      Select Top (1)
             *
        From ContactMechanism CM
       Where CM.Party_ID             = POSDocument.Customer_ID
         And ContactMechanismType    = 'Email'
         And ContactMechanismPurpose = 'Business'
  ) As CM
 Outer APPLY
  (
      Select Top (1)
             *
        From ContactMechanism CM
       Where CM.Party_ID             = POSDocument.Customer_ID
         And ContactMechanismType    = 'Phone'
         And ContactMechanismPurpose = 'Business'
  ) As EM
 Where (POSDocumentDetail.LineType = 5)
   And (POSDocument.DocumentType   = 4)
   And (POSDocumentDetail.isVoid   = 0)
 Order By
    POSDocument.Customer_ID

There is a further problem - the TOP 1 really needs an ORDER BY. The order by insures you get the expected results if there can be multiple rows returned.

1 Like
3

Thank you, that is exactly what I needed.