#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2007
    Posts
    7
    Rep Power
    0

    Help finding more than 1 occurrence in a table


    Hello! I need help finding cities with more than 1 postal code.

    Table: main
    Columns:
    - cities
    - streets
    - postalcodes

    Thanks a lot!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Code:
    SELECT cities
      FROM main
    GROUP
        BY cities
    HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    aaaack! slight correction....
    Code:
    SELECT cities
      FROM main
    GROUP
        BY cities
    HAVING COUNT(DISTINCT postalcodes) > 1
    this is assuming, of course, that the column named "postalcodes" actually has only one postalcode in each value (instead of, say, a comma-delimited list of postalcodes)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo