Contacts List

SELECT T.OrganisationV6 AS [COMPANY],
       c.ContactName AS [CONTACT NAME],
       c.FirstName AS [FIRSTNAME],
       c.MidName AS [MIDDLE NAME],
       c.Surname AS [SURNAME],
       c.Adr1 AS [ADDRESS 1],
       c.Adr2 AS [ADDRESS 1],
       c.Adr3 AS [ADDRESS 1],
       c.State AS [STATE],
       c.Country AS [COUNTRY],
       c.Postcode AS [POSTAL CODE],
       c.Email AS [EMAIL],
       V.VenueName AS [VENUE],
       v.address1 AS [VENUE ADDRESS],
       v.address2 AS [VENUE ADDRESS],
       v.city AS [VENUE CITY],
       v.state AS [VENUE STATE],
       v.country AS [VENUE COUNTRY],
       v.zipcode AS [VENUE ZIPCODE]
FROM tblContact c
LEFT OUTER JOIN tblLinkCustContact L ON C.ID = L.ContactID
LEFT OUTER JOIN tblVenues V ON C.ID = V.ContactID
LEFT OUTER JOIN tblCust T ON L.Customer_code = T.Customer_code
LEFT OUTER JOIN tblSalesper ON c.salesperson_code = tblSalesper.salesperson_code

/* Contacts excluding technicians */

SELECT DISTINCT

       c.contactname    AS [CONTACT NAME],

       c.firstname      AS [FIRSTNAME],

       c.midname        AS [MIDDLE NAME],

       c.surname        AS [SURNAME],

       c.adr1           AS [ADDRESS 1],

       c.adr2           AS [ADDRESS 1],

       c.adr3           AS [ADDRESS 1],

       c.state          AS [STATE],

       c.country        AS [COUNTRY],

       c.postcode       AS [POSTAL CODE],

       c.email          AS [EMAIL],

       CONCAT(ISNULL(LTRIM(RTRIM(c.Phone1CountryCode)), ''), ' ',  

              ISNULL(LTRIM(RTRIM(c.Phone1AreaCode)), ''),  ' ',  

              ISNULL(LTRIM(RTRIM(c.Phone1)), ''), ' ' ) 

        AS [CONTACT PHONE 1],

       CONCAT(ISNULL(LTRIM(RTRIM(c.Phone2CountryCode)), ''), ' ',  

              ISNULL(LTRIM(RTRIM(c.Phone2AreaCode)), ''),  ' ',  

              ISNULL(LTRIM(RTRIM(c.Phone2)), ''), ' ' ) 

        AS [CONTACT PHONE 2],

       CONCAT(ISNULL(LTRIM(RTRIM(c.CellCountryCode)), ''), ' ',  

              ISNULL(LTRIM(RTRIM(c.CellAreaCode)), ''),  ' ',  

              ISNULL(LTRIM(RTRIM(c.Cell)), ''), ' ' ) 

        AS [CONTACT CELL]

FROM   tblcontact c

       LEFT OUTER JOIN tbllinkcustcontact L

                    ON C.id = L.contactid

       LEFT OUTER JOIN tblvenues V

                    ON C.id = V.contactid

       LEFT OUTER JOIN tblcust T

                    ON L.customer_code = T.customer_code

       LEFT OUTER JOIN tblsalesper

                    ON c.salesperson_code = tblsalesper.salesperson_code

WHERE NOT EXISTS (

    SELECT 1

    FROM tblinvmas im

    WHERE im.ContactID = c.id

)