Did you really want a self-join query or do you just want to know which
addresses appear in more than one list?

Is (address_id, list_id) unique? If so, you should be able to do it this
way:

SELECT address_id
FROM Emails
WHERE list_id IN (1000,1001)
GROUP BY address_id
HAVING COUNT(*)>1

If (address_id, list_id) isn't unique, then:

SELECT address_id
FROM Emails
WHERE list_id IN (1000,1001)
GROUP BY address_id
HAVING MIN(list_id)<MAX(list_id)

It helps if you post proper DDL (CREATE TABLE statement) for your table and
include any keys and constraints. That way we don't have to guess at
signifcant aspects of your data.

I guess you could also do it with a self join like this:

SELECT DISTINCT E1.address_id
FROM Emails AS E1
JOIN Emails AS E2
ON E1.address_id = E2.address_id
AND E1.list_id=1000
AND E2.list_id=1001

--
David Portas
SQL Server MVP
--