http://stackoverflow.com/questions/5042021/ms-sql-nested-selects-mysterious-invalid-column-name-error

ms-sql-nested-selects-mysterious-invalid-column-name-error.sql

SELECT 
	DISTINCT(Email)
FROM
	Activity a,
	Consumer c,
	ActivityType t
WHERE
	c.CountryID = '23'
	AND t.ActivityType = 'Something_Create'
	AND a.ActivityTypeId = t.ActivityTypeId
	AND c.ConsumerID = a.ConsumerID
	AND (SELECT TOP 1 ActivityID
		FROM Activity aa, ActivityType tt 
		WHERE aa.ActivityTypeId = tt.ActivityTypeId 
			AND aa.ConsumerID = c.ConsumerID
			AND tt.ActivityType = 'Something_OptIn') > 0
	AND (SELECT TOP 1 ActivityID
		FROM Activity aa, ActivityType tt 
		WHERE aa.ActivityTypeId = tt.ActivityTypeId 
			AND aa.ConsumerID = c.ConsumerID
			AND tt.ActivityType = 'Something_OptOut') = NULL

-- Returns 0 rows
--------------------------

--------------------------
-- Works fine

SELECT 
	DISTINCT(Email)
FROM
	Activity a,
	Consumer c,
	ActivityType t
WHERE
	c.CountryID = '23'
	AND t.ActivityType = 'Something_Create'
	AND a.ActivityTypeId = t.ActivityTypeId
	AND c.ConsumerID = a.ConsumerID
	AND (SELECT TOP 1 ActivityID
		FROM Activity aa, ActivityType tt 
		WHERE aa.ActivityTypeId = tt.ActivityTypeId 
			AND aa.ConsumerID = c.ConsumerID
			AND tt.ActivityType = 'Something_OptIn') > 0