By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

Self Join Question

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
--
Jul 20 '05 #2

P: n/a
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.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.