/********************************************************************
All Medec Registrants who do NOT receive the Medeconomics Bulletin
*********************************************************************/
(
/******************
Medec Registrants
*******************/
SELECT DISTINCT
	u.userid
	,u.usertitle
	,u.userfirstname
	,u.userlastname
    ,ltrim(rtrim(lower(u.useremail)))     AS userEmail
FROM 
      dbo.tbl_Users u
INNER JOIN 
      u.f_sub_site ss ON u.userID = ss.userID
WHERE 
      ss.d_sub_type_id = 3    -- 3 = free trial
      AND ss.magJournalCode IN (
            'GP'
            ,'MM2'
            )
)
EXCEPT		-- exclude everyone from below from the above query
(
/**********************************
Users who receive Medec bulletin
***********************************/
SELECT DISTINCT
	u.userid
	,u.usertitle
	,u.userfirstname
	,u.userlastname
	,ltrim(rtrim(lower(u.useremail)))	AS userEmail
FROM
 tbl_Users U
 INNER JOIN (
  SELECT
   tusers.userId AS userId
   ,eusers.ecircleGID
   ,eusers.ecircledateadded AS  dateadded
   ,ecast.cmpID AS cmpID
  FROM
   [HBPL_ECircle].[tbl_ECircleUsers] AS eusers
   INNER JOIN [dbo].[tbl_ECast_Campaigns] AS ecast ON eusers.[ecircleGID] = ecast.[ecircleGID]
   INNER JOIN [dbo].[tbl_Users] AS tusers ON eusers.[userUUID] = tusers.[userUID]
  WHERE
   ecast.cmpID IN (1826)	-- Medec campaign code
  UNION ALL
  SELECT
   rpt_userID AS userID
   ,NULL AS ecircleGID
   ,rptdateadded AS dateadded
   ,rpt_cmpid AS cmpID
  FROM
   [dbo].[tbl_ECast_Recipients] AS erecip
  WHERE
   erecip.rpt_cmpID IN (1826)	-- Medec campaign code
  ) AS E ON U.[userID] = E.[userID]

WHERE
 U.userOptInHBPLEmails = 1	-- HM email opt in
)

.....
WHERE
 U.userOptInHBPLEmails = 1	-- HM email opt in
)
ORDER BY userid;

quote:

---

Originally posted by James K

At the very end:

.....
WHERE
 U.userOptInHBPLEmails = 1	-- HM email opt in
)
ORDER BY userid;

---