Self Join Question

[Tech]

I have a table tblEmails where
the columns are id,list_id,address_id. I have many lists. I need to
find out
if a couple of lists (list_ids - 1000,1001,1002) have same
address_ids in common or not.
Snaphot

1,1000,1234
2,1000,2345
3,1001,4567
4,1001,1234

now the query should return 1 record if I give 1000 and 1001 as
list_ids.

[David Portas]

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
--

[Fred]

Select tblEmails.list_id
From tblEmails INNER JOIN
tblEmails tblEmails_1 ON tblEmails.list_id = tblEmails_1.list_id
WHERE (tblEmails.list_id = 1000)

or something very similar.
ne********@yahoo.com (Tech) wrote in message news:<35*************************@posting.google.c om>...

I have a table tblEmails where
the columns are id,list_id,address_id. I have many lists. I need to
find out
if a couple of lists (list_ids - 1000,1001,1002) have same
address_ids in common or not.
Snaphot

1,1000,1234
2,1000,2345
3,1001,4567
4,1001,1234

now the query should return 1 record if I give 1000 and 1001 as
list_ids.