SELECT nlr.Person_ID
FROM NewsletterRecipient AS nlr
INNER JOIN Person AS p ON (p.Person_ID = nlr.Person_ID)
INNER JOIN ShopUser AS su ON (su.Person_ID = p.Person_ID)
WHERE
	nlr.NewsletterRecipient_Active = 'true'
	AND su.ShopUser_Active = 'true'
	AND su.ShopUser_IsAnoymous = 'false'
	AND nlr.Person_ID NOT IN (
		SELECT sc.Person_ID
		FROM ShopCustomer AS sc
		INNER JOIN ShopOrder AS so ON (so.ShopOrder_ID = sc.ShopOrder_ID)
		WHERE
			so.ShopOrderState_Code != 'canceled'
		GROUP BY sc.Person_ID
	)
