Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: Display Duplicates which have a different state

    Hi I have this query here which displays all duplicates which have the same phone number but i am trying to modify it to display only fields that have the same phone number but have different state

    SELECT *
    FROM businesses t1
    join (SELECT ce_phone FROM businesses GROUP BY ce_phone HAVING COUNT(*)>1) t2
    ON t1.ce_phone = t2.ce_phone
    WHERE t1.ce_phone is not null
    ORDER BY t1.ce_phone

    So what I would try to do would be to group state within the ce_phone group and count the state duplicates and if it is less than the count of phone then i would display it but i am just not sure how to write the syntax for it.

    If anyone can set me in the right direction it would be much appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ce_phone 
      FROM businesses 
    GROUP 
        BY ce_phone 
    HAVING COUNT(*) > 1
       AND COUNT(DISTINCT state) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    7
    Thankyou r937 that worked well

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •