complex problem

[rob]

Hi all

I have a problem I could use some help with. (I'm new to SQL).

I have a table with 4000+ user details, incuding email addresses. I
need to send out one email per domain except where the domain is an
ISP, or email provider such as hotmail. The best way for me to do
this would be to show all the records where a domain has more than one
email address on it, and then manually go through and delete
superfluous records from the domains which are not ISP's or email
providers.

I need suggestions on how best to tackle this. I've split up the
email column into two seperate columns : username and domain. I've
tried the following :

SELECT count(*) AS num, id, firstname,lastname,username,domain FROM
users
GROUP BY domain HAVING num > 1;

but this only returns one record per domain. I need to return all
records where a domain has more than one email address so I can
selectivly delete the records I don't need.

I'm pretty sure the answer lies in creating a seperate 'domains' table
and setting up a relationship between domains and users, but my
attempts so far have been fruitless.

could anyone point me in the right direction?

thanks
robs

[sdfgsd]

"rob" <ro******@hotmail.com> wrote in message
news:31**************************@posting.google.c om...

Hi all

I have a problem I could use some help with. (I'm new to SQL).

I have a table with 4000+ user details, incuding email addresses. I
need to send out one email per domain except where the domain is an
ISP, or email provider such as hotmail. The best way for me to do
this would be to show all the records where a domain has more than one
email address on it, and then manually go through and delete
superfluous records from the domains which are not ISP's or email
providers.
I'm not sure I understand the problem, but...see below.
I need suggestions on how best to tackle this. I've split up the
email column into two seperate columns : username and domain. I've
tried the following :

SELECT count(*) AS num, id, firstname,lastname,username,domain FROM
users
GROUP BY domain HAVING num > 1;

Just "GROUP BY domain". HAVING cannot refer to
I think you'll need to get a little dirty on this if you're not using a
programming/scripting tool.
Just "GROUP BY domain".

<snip>