SQL help: SELECT DISTINCT records while ignoring unique records

[jimoblak]

I have a table like this:

Joe Bob | jbob@mail.com
Carl Landry | clandry@mail.com
Susan Weismann | sweismann@mail.com
Joe Bob | jbob@mail.com
Joe Bob | jbob@mail.com
Carl Landry | clandry@mail.com

How would I select the distinct email addresses while ignoring the records where an email address only appears once?

Example output:

jbob@mail.com
clandry@mail.com

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[sleipnir214]

That query would be a lot easier if you had unique record ids for each record in that table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[jimoblak]

Record IDs were left out to make the table seem simpler for my question:

1 | Joe Bob | jbob@mail.com
2 | Carl Landry | clandry@mail.com
3 | Susan Weismann | sweismann@mail.com
4 | Joe Bob | jbob@mail.com
5 | Joe Bob | jbob@mail.com
6 | Carl Landry | clandry@mail.com

I think that using the following query:
SELECT Email, COUNT(*) FROM table GROUP BY Email

...and then using PHP to print only those email addresses with a COUNT(*) of 2 or more should work.

Is there a better way to produce this output in MySQL only, without the need for PHP to filter extra records?

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[jimoblak]

The following MySQL query and PHP code seemed to be efficient enough for the amount of records I'm working with.

<?php
$Link = mysql_connect ($Host, $User, $Password);
$Query = &quot;SELECT Email, COUNT(*) FROM table GROUP BY Email&quot;;
$Result = mysql_db_query ($DBName, $Query, $Link);

while ($Row = mysql_fetch_array ($Result)) {
if ($Row['COUNT(*)']>=2) {
print (&quot;$Row[Email] - - $Row[COUNT(*)] <br>\n&quot;);
}
}

mysql_close ($Link);
?>

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[hos2]

what is the problem of using the distinct with email adresses that appear more than 1 time ???

 

[sleipnir214]

I'd use a left join.


select
distinct t1.email
From
emails t1
left join
emails t2
on
t1.email = t2.email
where
t1.id != t2.id




Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

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

 

[jimoblak]

Thanks all!

swamp's query executes in half the time of my own reckless SQL and eliminates the need for the secondary PHP filtering. I have yet to attempt the join.

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[tshot]

Hi sleipnir214 i would have used swampBoogie 's method. Yur option did not occur to me till i read it. Now which would be a better option to choose




----------------
ur feedback is a very welcome desire

 

[r937]

Now which would be a better option to choose

i think swampboogie's is much better, because when you read it, you see immediately what it's doing

there are instances, though, where the self-join is useful, so don't just dismiss it, it may come in handy some day





rudy

http://r937.com/

SQL Consulting