CODEWITHSUNDEEP
sqldatabase
Brian
Brian

Reputation: 211

Database Query for Multiple Instances

I have a database table of all US zip codes and their corresponding state and congressional district like this below..

id | zipcode | state_abbr | district
 1     30080      GA           1
 2     30080      TN           2

I need a query that will return any zipcodes that show up in more than one state. How can I do this?

Upvotes: 0

Views: 61

Answers (3)

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this sql.

SQL Fiddle

MySQL 5.5.30 Schema Setup:

CREATE TABLE Table1
    (`id` int, `zipcode` int, `state_abbr` varchar(2), `district` int)
;

INSERT INTO Table1
    (`id`, `zipcode`, `state_abbr`, `district`)
VALUES
    (1, 30080, 'GA', 1),
    (2, 30080, 'TN', 2)
;

Query 1:

select zipcode
from Table1
group by zipcode
having count(zipcode)>1

Results:

| ZIPCODE |
-----------
|   30080 |

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

SELECT DISTINCT x.zipcode 
  FROM zipcode x 
  JOIN zipcode y 
    ON y.zipcode = x.zipcode 
   AND y.id < x.id;

Upvotes: 0

Jared Peless
Jared Peless

Reputation: 1120

SELECT zipcode 
FROM (
       SELECT zipcode 
       FROM temp
       GROUP BY zipcode, state_abbr 
     ) AS t 
GROUP BY zipcode 
HAVING COUNT(*) > 1

Upvotes: 2

Related Questions