Selecting Certain Records, Should be Easy

[NateUNI]

I have the following records (small example),

EMAIL SUBMIT_DATE EGROUP
----- ----------- ------
1@hotmail.com 01/24/2003 01:17:45 BUS-NP
2@hotmail.com 01/24/2003 01:17:45 BUS-NP
3@hotmail.com 01/24/2003 01:17:45 BUS-NP
4@hotmail.com 01/24/2003 01:17:45 BUS-SP
3@hotmail.com 01/25/2003 01:17:45 BUS-NP
4@hotmail.com 01/25/2003 01:17:45 BUS-SP

What I want to do is pull all the email addresses that appear on more than one date, ie. 3@hotmail.com and 4@hotmail.com

This is what I have so far:

SELECT UNIQUE (EMAIL), SUBMIT_DATE, EGROUP
FROM PROSPECT_EMAIL
WHERE (EGROUP = 'BUS-NP' OR EGROUP = 'BUS-HU' OR EGROUP = 'BUS-SP')
GROUP BY EMAIL, SUBMIT_DATE, EGROUP
ORDER BY SUBMIT_DATE, EGROUP

but does not work? anyone have any ideas, THANKS!

 

[DBAwhosaysNIE]

select email
from prospect_email
group by email
having count(*)>1

 

[DBAwhosaysNIE]

or if you want all the columns (which your sample code indicated but your post didn't):

select *
from prospect_email
where email in
(select email
from prospect_email
group by email
having count(*)>1
)