SQL query using table aliases

Question

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?

Why do you have city c3 in the query at all?

Adriaan Stander
– Adriaan Stander

2012-08-06 05:23:08 +00:00
Commented Aug 6, 2012 at 5:23 — Adriaan Stander
– Adriaan Stander, Commented Aug 6, 2012 at 5:23
Question has been adjusted. Thanks for that.

Teererai Marange
– Teererai Marange

2012-08-06 05:23:40 +00:00
Commented Aug 6, 2012 at 5:23 — Teererai Marange
– Teererai Marange, Commented Aug 6, 2012 at 5:23

Barmar · Accepted Answer · 2012-08-06 05:22:39Z

3

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

answered Aug 6, 2012 at 5:22

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lexu Over a year ago

shouldn't that be c1.name < c2.name? That way cities in the same country will show up ... and cities in separate countries won't show up twice.

Barmar Over a year ago

His original SQL says country. In any case, the point of the solution is that for whatever column you want to be different, use < instead of !=. This ensures that you get just one row for each pair rather than 2. Going by the example output he gave, I suspect what he needs is c1.country = c2.country AND c1.name < c2.name.

lexu Over a year ago

he's also complaining that the cities show up twice. once in the c1 table and once in the c2 table. by forcing the name in c1 to be smaller than the name in c2 a repeat is prevented.... I see now you realised that too. Why don't you add that to your answer?

Teererai Marange Over a year ago

Thanks guys. My original question was incorrect. I wanted the cities to come from different countries so Barmars answer was correct. However I learnt from all answers so thanks for your help.

Omesh · Accepted Answer · 2012-08-06 05:30:05Z

0

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;

answered Aug 6, 2012 at 5:30

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Comments

Mihai Matei · Accepted Answer · 2012-08-06 05:50:21Z

-1

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

edited Aug 6, 2012 at 5:50

answered Aug 6, 2012 at 5:30

Mihai Matei

24.3k5 gold badges34 silver badges50 bronze badges

3 Comments

Barmar Over a year ago

What makes you think he has duplicate records in his table? The duplication only comes when he does the self-join with a != condition, because you get pairs of rows where A!=B or B!=A. That's why changing the condition to < solves it.

Mihai Matei Over a year ago

I didn't said about what the used query returns. I said that in his cities table he store New York, USA; Los Angeles, USA where USA is a duplicate entry in this row and maybe in others, which is not correctly entry for a strong database. So he should store just unique city names having relation with the id of the country.

Barmar Over a year ago

So you weren't actually answering his question, just making a general suggestion about his database design. That belongs in a comment, not an answer.

Collectives™ on Stack Overflow

SQL query using table aliases

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related