CODEWITHSUNDEEP
mysqlsqldatabase
rubikskube
rubikskube

Reputation: 402

MySql - Find values that occur AT LEAST two times without using any aggregate function

Table:City

City_Name (PK)    State_Name (PK)    Population
--------------    ---------------    ----------
Austin            MA
Austin            TX
Burlington        VT
Fresno            CA
Houston           TX
Independence      KS
Independence      MO
Independence      PA 
Kansas            CA 
Kansas            MO
Paris             MO 
Paris             OR 
Paris             TX 
Paris             VA  
Paso              TX

The above table has a composite primary key (City_Name and State_Name). I want to find the city which occur in at least two distinct States. In this case, the result will be

City_Name
---------
Austin
Independence
Kansas
Paris

Other cities will not qualify as they don't occur in at-least two states.

This solution is required for practicing relational calculus problems and thus (unfortunately) aggregate function such as COUNT() cannot be used. I went through a database book where I saw a solution to similar problem which looks like this, but not working.

SELECT c1.State_Name FROM City AS c1
JOIN City AS c2 on c1.City_Name = c2.City_Name AND c1.State_Name = c2.State_Name
JOIN City AS c3 on c1.City_Name = c3.City_Name AND c1.State_Name = c3.State_Name
WHERE (c2.City_Name<>c3.City_Name) AND (c2.State_Name<>c3.State_Name);

Some help at this point will be highly welcoming.

Upvotes: 2

Views: 2498

Answers (5)

mvp
mvp

Reputation: 116447

SELECT city FROM mytable
GROUP BY city
HAVING count(city) > 1

SQLFiddle Demo

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You could use an exists subquery:

select  city_name 
from    city c1
where   exists 
        (
        select  *
        from    city c2
        where   c1.city_name = c2.city_name 
                and c1.state_name <> c2.state_name
        )

Upvotes: 1

VMai
VMai

Reputation: 10346

You can use COUNT(DISTINCT) and restrict your result with the HAVING clause:

SELECT City_Name, COUNT(DISTINCT State_Name)
FROM City
GROUP BY City_NAME
HAVING COUNT(DISTINCT State_Name) > 1

see documentation of COUNT(DISTINCT)

Returns a count of the number of rows with different non-NULL expr values.

I overlooked the restriction not be able to use COUNT(). That leads indeed to a self join:

SELECT
    DISTINCT c1.City_Name
FROM
    City c1
INNER JOIN
    City c2
ON
    c1.City_Name = c2.City_Name
AND
    c1.State_Name <> c2.State_Name

Demo for both solutions.

Upvotes: 6

Dmitry Bezik
Dmitry Bezik

Reputation: 399

SELECT City_Name FROM City GROUP BY City_Name HAVING COUNT(City_Name) > 1;

Upvotes: 1

ffflabs
ffflabs

Reputation: 17511

If you can't use any kind of aggregate functions, then the following would return the cities that appear at least twice

SELECT distinct c1.city_name 
  FROM `city` c1 
  JOIN city c2 
        ON c1.city_name=c2.city_name 
       AND c1.state_name!=c2.state_name

I assumed that not only COUNT but also any kind of aggegate was unavailable

Upvotes: 5

Related Questions