Comparing two columns in postgres database

3826 views sql
7

I am somewhat stumped on a seemingly simple problem. I have a table set up such as,

CREATE TABLE cities (
     column_1 TEXT,
     column_2 TEXT);

Where the contents of these tables look like

column_1  |  column_2
---------------------
Atlanta   |  Atlanta
Boston    |  Chicago
Chicago   |  Los Angeles
Seattle   |  Tacoma
NULL      |  Seattle

What query could I run that would look at both of these columns, and despite their order, return where the two columns matchup?

The result I am looking for would be:

column_1  |  column_2
---------------------
Atlanta   |  Atlanta
Chicago   |  Chicago
Seattle   |  Seattle

I have tried:

SELECT *
FROM cities
WHERE column_1 = column_2;

But this only returns the EXACT matches:

column_1  |  column_2
---------------------
Atlanta   |  Atlanta

answered question

2 Answers

5

You just need a self join:

SELECT c1.column_1, c2.column_2
FROM cities c1
JOIN cities c2
    ON c1.column_1 = c2.column_2

posted this
0

your query is asking the question where is column_1 == column_2 in the same row. If you want to retrieve all instances of cities existing in both columns you would need to do a self join like so.

SELECT t1.column_1, t2.column_2
FROM cities t1 join cities t2 on (t1.column_1 = t2.column_2)

posted this

Have an answer?

JD

Please login first before posting an answer.