CODEWITHSUNDEEP
mysqlsqldatabase
Teererai Marange
Teererai Marange

Reputation: 2132

SQL query using table aliases

I have a created a table named city in my database. This table has 2 columns, called 'name' and 'country'. I have created a query that returns the combinations of cities from different countries which is below: 

SELECT c1.name, c1.country, c2.name, c2.country 
FROM city c1, city c2
WHERE c1.country != c2.country

This query works, but however the city pairs are repeated ie. I get results with:

    Berlin Germany London England 
London England Berlin Germany

which means that the city pair berlin/hamburg is repeated in my result set. Is there a way around this?

Upvotes: 2

Views: 378

Answers (3)

Mihai Matei
Mihai Matei

Reputation: 24276

A good database will never have two or more same records in a single table. So my recommendation is two make 2 tables:

cities: ID, country_ID, Name
countries: ID, Name

and the select should be:

SELECT c.Name, co.Name
FROM cities c
INNER JOIN countries co
    ON co.ID=c.country_ID

Upvotes: -1

Omesh
Omesh

Reputation: 29071

You can do this by using LEFT JOIN see Visual Explanation Of Joins try this:

SELECT c1.name, c1.country, c2.name, c2.country 
FROM city c1
     LEFT JOIN city c2
         ON c1.country = c2.country 
            AND c1.name = c2.name
WHERE c1.country IS NULL OR 
      c2.country IS NULL;

Upvotes: 0

Barmar
Barmar

Reputation: 780714

SELECT c1.name, c1.country, c2.name, c2.country 
FROM city c1, city c2
WHERE c1.country < c2.country

Upvotes: 3

Related Questions