complex problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rob
    #1

    complex problem

    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,lastn ame,username,do main 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
    Tags: None
    • sdfgsd
      #2
      Re: complex problem


      "rob" <robtaway@hotma il.com> wrote in message
      news:317bfb14.0 311030207.7efae a93@posting.goo gle.com...[color=blue]
      > 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.[/color]

      I'm not sure I understand the problem, but...see below.
      [color=blue]
      > 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,lastn ame,username,do main FROM
      > users
      > GROUP BY domain HAVING num > 1;[/color]

      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>


        Comment

        Previous template Next
        Working...